def test_verticaoracle_multiple_rollups(self):
        q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
                                           fn.Sum(self.table.bar)).rollup(
                                               self.table.foo, ).rollup(
                                                   self.table.fiz, )

        self.assertEqual(
            'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo"),ROLLUP("fiz")',
            str(q))
Esempio n. 2
0
    def test_orderby_alias(self):
        bar = self.t.bar.as_('bar01')
        q = Query.from_(self.t) \
            .select(fn.Sum(self.t.foo), bar) \
            .orderby(bar)

        self.assertEqual(
            'SELECT SUM("foo"),"bar" "bar01" FROM "abc" ORDER BY "bar01"',
            q.get_sql())
Esempio n. 3
0
    def test_groupby__no_alias_platforms(self):
        bar = self.t.bar.as_("bar01")
        for query_cls in [MSSQLQuery, OracleQuery]:
            q = query_cls.from_(self.t).select(fn.Sum(self.t.foo),
                                               bar).groupby(bar)

            self.assertEqual(
                'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"',
                str(q))
    def test_verticaoracle_from_parameters(self):
        q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
                                           fn.Sum(self.table.bar)).groupby(
                                               self.table.foo, ).rollup(
                                                   self.table.fiz, )

        self.assertEqual(
            'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY "foo",ROLLUP("fiz")',
            str(q))
    def test_select_field_from_missing_table(self):
        with self.assertRaises(JoinException):
            Query.from_(self.table_abc).select(self.table_efg.foo)

        with self.assertRaises(JoinException):
            Query.from_(self.table_abc).where(self.table_efg.foo == 0)

        with self.assertRaises(JoinException):
            Query.from_(self.table_abc).where(fn.Sum(self.table_efg.foo) == 0)

        with self.assertRaises(JoinException):
            Query.from_(self.table_abc).select(fn.Sum(self.table_abc.bar * 2) + fn.Sum(self.table_efg.foo * 2))

        with self.assertRaises(JoinException):
            Query.from_(self.table_abc).groupby(self.table_efg.foo)

        with self.assertRaises(JoinException):
            Query.from_(self.table_abc).groupby(self.table_abc.foo).having(self.table_efg.bar)
Esempio n. 6
0
    def test_groupby__no_alias(self):
        bar = self.t.bar.as_('bar01')
        q = Query.from_(self.t) \
            .select(fn.Sum(self.t.foo), bar) \
            .groupby(bar)

        self.assertEqual(
            'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"',
            q.get_sql(groupby_alias=False))
Esempio n. 7
0
    def test_having_join_and_equality(self):
        q = (
            Query.from_(self.table_abc)
            .join(self.table_efg)
            .on(self.table_abc.foo == self.table_efg.foo)
            .select(self.table_abc.foo, fn.Sum(self.table_efg.bar), self.table_abc.buz)
            .groupby(self.table_abc.foo)
            .having(self.table_abc.buz == "fiz")
            .having(fn.Sum(self.table_efg.bar) > 100)
        )

        self.assertEqual(
            'SELECT "abc"."foo",SUM("efg"."bar"),"abc"."buz" FROM "abc" '
            'JOIN "efg" ON "abc"."foo"="efg"."foo" '
            'GROUP BY "abc"."foo" '
            'HAVING "abc"."buz"=\'fiz\' AND SUM("efg"."bar")>100',
            str(q),
        )
Esempio n. 8
0
    def _test_rounded_timeseries(self, increment):
        rounded_dt = settings.database.round_date(self.mock_table.dt, increment)

        return self.manager._build_data_query(
            table=self.mock_table,
            joins=[],
            metrics=OrderedDict([
                ('clicks', fn.Sum(self.mock_table.clicks)),
                ('roi', fn.Sum(self.mock_table.revenue) / fn.Sum(self.mock_table.cost)),
            ]),
            dimensions=OrderedDict([
                ('date', rounded_dt)
            ]),
            mfilters=[],
            dfilters=[],
            references={},
            rollup=[],
        )
Esempio n. 9
0
    def test_groupby__sum_distinct(self):
        q = (
            Query.from_(self.t)
            .groupby(self.t.foo)
            .select(self.t.foo, fn.Sum(self.t.bar).distinct())
        )

        self.assertEqual(
            'SELECT "foo",SUM(DISTINCT "bar") FROM "abc" GROUP BY "foo"', str(q)
        )
Esempio n. 10
0
    def test_groupby__sum_filter(self):
        q = (Query.from_(self.t).groupby(self.t.foo).select(
            self.t.foo,
            fn.Sum(self.t.bar).filter(self.t.id.eq(1) & self.t.cid.gt(2)),
        ))

        self.assertEqual(
            'SELECT "foo",SUM("bar") FILTER(WHERE "id"=1 AND "cid">2) FROM "abc" GROUP BY "foo"',
            str(q),
        )
Esempio n. 11
0
    def test_prefixes_added_to_groupby(self):
        test_query = Query.from_(self.table_abc).join(self.table_efg).on(
            self.table_abc.foo == self.table_efg.bar).select(
                self.table_abc.foo,
                fn.Sum(self.table_efg.buz)).groupby(self.table_abc.foo)

        self.assertEqual(
            'SELECT "abc"."foo",SUM("efg"."buz") FROM "abc" '
            'JOIN "efg" ON "abc"."foo"="efg"."bar" '
            'GROUP BY "abc"."foo"', str(test_query))
Esempio n. 12
0
    def test_having_join_and_equality(self):
        q = Query.from_(self.table_abc).join(
            self.table_efg
        ).on(
            self.table_abc.foo == self.table_efg.foo
        ).select(
            self.table_abc.foo, fn.Sum(self.table_efg.bar), self.table_abc.buz
        ).groupby(
            self.table_abc.foo
        ).having(
            self.table_abc.buz == 'fiz'
        ).having(
            fn.Sum(self.table_efg.bar) > 100
        )

        self.assertEqual('SELECT "abc"."foo",SUM("efg"."bar"),"abc"."buz" FROM "abc" '
                         'JOIN "efg" ON "abc"."foo"="efg"."foo" '
                         'GROUP BY "abc"."foo" '
                         "HAVING \"abc\".\"buz\"='fiz' AND SUM(\"efg\".\"bar\")>100", str(q))
Esempio n. 13
0
    def test_mysql_rollup_two_groupbys(self):
        q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
                                           fn.Sum(self.table.bar)).rollup(
                                               self.table.foo,
                                               self.table.fiz,
                                               vendor='mysql')

        self.assertEqual(
            'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY "foo","fiz" WITH ROLLUP',
            str(q))
Esempio n. 14
0
    def test_metrics(self):
        query = self.manager._build_data_query(
            table=self.mock_table,
            joins=[],
            metrics=OrderedDict([
                ('clicks', fn.Sum(self.mock_table.clicks)),
                ('roi', fn.Sum(self.mock_table.revenue) /
                 fn.Sum(self.mock_table.cost)),
            ]),
            dimensions={},
            mfilters=[],
            dfilters=[],
            references={},
            rollup=[],
        )

        self.assertEqual(
            'SELECT SUM("clicks") "clicks",SUM("revenue")/SUM("cost") "roi" '
            'FROM "test_table"', str(query))
Esempio n. 15
0
    def test_metrics_with_joins(self):
        rounded_dt = settings.database.round_date(self.mock_table.dt, 'DD')
        locale = self.mock_table.locale

        query = self.manager._build_data_query(
            table=self.mock_table,
            joins=[
                (self.mock_join1,
                 self.mock_table.hotel_id == self.mock_join1.hotel_id,
                 JoinType.left),
            ],
            metrics=OrderedDict([
                ('clicks', fn.Sum(self.mock_table.clicks)),
                ('roi', fn.Sum(self.mock_table.revenue) /
                 fn.Sum(self.mock_table.cost)),
                ('hotel_name', self.mock_join1.hotel_name),
                ('hotel_address', self.mock_join1.address),
                ('city_id', self.mock_join1.ctid),
                ('city_name', self.mock_join1.city_name),
            ]),
            dimensions=OrderedDict([
                ('date', rounded_dt),
                ('locale', locale),
            ]),
            mfilters=[],
            dfilters=[],
            references={},
            rollup=[],
        )

        self.assertEqual(
            'SELECT '
            'ROUND("test_table"."dt",\'DD\') "date","test_table"."locale" "locale",'
            'SUM("test_table"."clicks") "clicks",'
            'SUM("test_table"."revenue")/SUM("test_table"."cost") "roi",'
            '"test_join1"."hotel_name" "hotel_name","test_join1"."address" "hotel_address",'
            '"test_join1"."ctid" "city_id","test_join1"."city_name" "city_name" '
            'FROM "test_table" '
            'LEFT JOIN "test_join1" ON "test_table"."hotel_id"="test_join1"."hotel_id" '
            'GROUP BY ROUND("test_table"."dt",\'DD\'),"test_table"."locale" '
            'ORDER BY ROUND("test_table"."dt",\'DD\'),"test_table"."locale"',
            str(query))
    def test_verticaoracle_from_two_groupbys(self):
        q = (Query.from_(self.table).select(self.table.foo, self.table.fiz,
                                            fn.Sum(self.table.bar)).rollup(
                                                self.table.foo,
                                                self.table.fiz,
                                            ))

        self.assertEqual(
            'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo","fiz")',
            str(q),
        )
Esempio n. 17
0
    def test_groupby__alias_with_join(self):
        table1 = Table('table1', alias='t1')
        bar = table1.bar.as_('bar01')
        q = Query.from_(self.t) \
            .join(table1).on(self.t.id == table1.t_ref) \
            .select(fn.Sum(self.t.foo), bar) \
            .groupby(bar)

        self.assertEqual('SELECT SUM("abc"."foo"),"t1"."bar" "bar01" FROM "abc" '
                         'JOIN "table1" "t1" ON "abc"."id"="t1"."t_ref" '
                         'GROUP BY "bar01"', str(q))
Esempio n. 18
0
    def setUpClass(cls):
        test_table = Table('test_table')
        cls.test_slicer = Slicer(
            table=test_table,
            database=TestDatabase(),

            metrics=[
                # Metric with defaults
                Metric('clicks', 'Clicks'),
                Metric('conversions', 'Conversions'),
                Metric('roi', 'ROI', definition=fn.Sum(test_table.revenue) / fn.Sum(test_table.cost)),
                Metric('rpc', 'RPC', definition=fn.Sum(test_table.revenue) / fn.Sum(test_table.clicks)),
                Metric('cpc', 'CPC', definition=fn.Sum(test_table.cost) / fn.Sum(test_table.clicks)),
            ],

            dimensions=[
                # Continuous date dimension
                DatetimeDimension('date', definition=test_table.dt),

                # Continuous integer dimension
                ContinuousDimension('clicks', label='Clicks CUSTOM LABEL', definition=test_table.clicks),

                # Categorical dimension with fixed options
                CategoricalDimension('locale', 'Locale', definition=test_table.locale,
                                     display_options=[DimensionValue('us', 'United States'),
                                                      DimensionValue('de', 'Germany')]),

                # Unique Dimension with single ID field
                UniqueDimension('account', 'Account', definition=test_table.account_id,
                                display_field=test_table.account_name),
            ]
        )

        cls.test_slicer.manager.data = Mock()
        cls.test_slicer.manager.display_schema = Mock()
Esempio n. 19
0
    def __init__(self, da=None):
        super().__init__(da=da)
        self.formats.update({
            'Total': '{:,.0f}',
            'SMS': '{:,.0f}',
            'Suncor': '{:,.0f}',
            'SMS %': '{:.2%}',
            'Suncor %': '{:.2%}'
        })
        a, b = self.a, self.b

        total = fn.Sum(a.Duration).as_('Total')
        sum_sms = fn.Sum(a.SMS).as_('Sum_SMS')
        sum_suncor = fn.Sum(a.Suncor).as_('Sum_Suncor')

        cols = [a.CategoryAssigned, total, sum_sms, sum_suncor]

        q = self.q \
            .groupby(a.CategoryAssigned) \
            .orderby(total) \

        f.set_self(vars())
Esempio n. 20
0
    def test_use_double_quotes_on_alias_but_not_on_terms(self):
        idx = self.table_abc.index.as_("idx")
        val = fn.Sum(self.table_abc.value).as_("val")
        q = SnowflakeQuery.from_(self.table_abc).select(
            idx, val).groupby(idx).orderby(idx)

        self.assertEqual(
            'SELECT index "idx",SUM(value) "val" '
            "FROM abc "
            'GROUP BY "idx" '
            'ORDER BY "idx"',
            q.get_sql(with_namespace=True),
        )
Esempio n. 21
0
def _indicator(q: QueryBuilder, indicator: ReportIndicator, column: Column) -> QueryBuilder:
    if indicator.arithmetic == "sum":
        return q.select(fn.Sum(parse_parameter(column.parameter)))
    elif indicator.arithmetic == "avg":
        return q.select(fn.Avg(parse_parameter(column.parameter)))
    elif indicator.arithmetic == "max":
        return q.select(fn.Max(parse_parameter(column.parameter)))
    elif indicator.arithmetic == "min":
        return q.select(fn.Min(parse_parameter(column.parameter)))
    elif indicator.arithmetic == "count":
        return q.select(fn.Count(parse_parameter(column.parameter)))
    else:
        return q.select(fn.Max(parse_parameter(column.parameter)))
Esempio n. 22
0
    def test_metrics_filters(self):
        query = self.manager._build_data_query(
            table=self.mock_table,
            joins=[],
            metrics=OrderedDict([
                ('clicks', fn.Sum(self.mock_table.clicks)),
                ('roi', fn.Sum(self.mock_table.revenue) /
                 fn.Sum(self.mock_table.cost)),
            ]),
            dimensions=OrderedDict([('device_type',
                                     self.mock_table.device_type)]),
            mfilters=[],
            dfilters=[self.mock_table.dt[date(2000, 1, 1):date(2001, 1, 1)]],
            references={},
            rollup=[],
        )

        self.assertEqual(
            'SELECT "device_type" "device_type",SUM("clicks") "clicks",SUM("revenue")/SUM("cost") "roi" '
            'FROM "test_table" '
            'WHERE "dt" BETWEEN \'2000-01-01\' AND \'2001-01-01\' '
            'GROUP BY "device_type" '
            'ORDER BY "device_type"', str(query))
Esempio n. 23
0
    def test_verticaoracle_func_partial(self):
        q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
                                           self.table.buz,
                                           fn.Sum(self.table.bar)).groupby(
                                               Rollup(
                                                   self.table.foo,
                                                   self.table.fiz,
                                               ),
                                               self.table.buz,
                                           )

        self.assertEqual(
            'SELECT "foo","fiz","buz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo","fiz"),"buz"',
            str(q))
Esempio n. 24
0
    def test_multidimension_categorical(self):
        query = self.manager._build_data_query(
            table=self.mock_table,
            joins=[],
            metrics=OrderedDict([
                ('clicks', fn.Sum(self.mock_table.clicks)),
                ('roi', fn.Sum(self.mock_table.revenue) / fn.Sum(self.mock_table.cost)),
            ]),
            dimensions=OrderedDict([
                ('device_type', self.mock_table.device_type),
                ('locale', self.mock_table.locale),
            ]),
            mfilters=[],
            dfilters=[],
            references={},
            rollup=[],
        )

        self.assertEqual(
            'SELECT "device_type" "device_type","locale" "locale",'
            'SUM("clicks") "clicks",SUM("revenue")/SUM("cost") "roi" '
            'FROM "test_table" '
            'GROUP BY "device_type","locale" '
            'ORDER BY "device_type","locale"', str(query))
Esempio n. 25
0
    def test_groupby_with_case_uses_the_alias(self):
        q = Query.from_(self.t).select(
            fn.Sum(self.t.foo).as_('bar'),
            Case().when(self.t.fname == "Tom",
                        "It was Tom").else_("It was someone else.").
            as_('who_was_it')).groupby(Case().when(
                self.t.fname == "Tom",
                "It was Tom").else_("It was someone else.").as_('who_was_it'))

        self.assertEqual(
            "SELECT SUM(\"foo\") \"bar\","
            "CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
            "ELSE 'It was someone else.' END \"who_was_it\" "
            "FROM \"abc\" "
            "GROUP BY \"who_was_it\"", str(q))
Esempio n. 26
0
    def setUp(self):
        subquery1 = Query.from_(self.table_abc).select(
            self.table_abc.foo,
            fn.Sum(self.table_abc.fizz + self.table_abc.buzz).as_('fizzbuzz'),
        ).groupby(self.table_abc.foo)

        subquery2 = Query.from_(self.table_efg).select(
            self.table_efg.foo.as_('foo_two'),
            self.table_efg.bar,
        )

        self.query = Query.from_(subquery1).select(
            subquery1.foo, subquery1.fizzbuzz).join(subquery2).on(
                subquery1.foo == subquery2.foo_two).select(
                    subquery2.foo_two, subquery2.bar)
Esempio n. 27
0
    def test_pagination_applied_with_orders(self, mock_fetch_data: Mock, mock_paginate: Mock, *mocks):
        mock_widget = f.Widget(slicer.metrics.votes)
        mock_widget.transform = Mock()

        # Need to keep widget the last call in the chain otherwise the object gets cloned and the assertion won't work
        slicer.data \
            .dimension(slicer.dimensions.timestamp) \
            .widget(mock_widget) \
            .orderby(slicer.metrics.votes, Order.asc) \
            .fetch()

        votes_definition_with_alias_matcher = PypikaQueryMatcher(fn.Sum(slicer.table.votes).as_('$d$votes'))
        orders = [(votes_definition_with_alias_matcher, Order.asc)]
        mock_paginate.assert_called_once_with(mock_fetch_data.return_value, [mock_widget],
                                              limit=None, offset=None, orders=orders)
def _indicator(q: QueryBuilder, indicator: ReportIndicator,
               column: Column) -> QueryBuilder:
    column_param = parse_parameter(column.parameter)
    value_ = column_param.get("value")
    if indicator.arithmetic == "sum":
        return q.select(fn.Sum(value_))
    elif indicator.arithmetic == "avg":
        return q.select(fn.Avg(value_))
    elif indicator.arithmetic == "max":
        return q.select(fn.Max(value_))
    elif indicator.arithmetic == "min":
        return q.select(fn.Min(value_))
    elif indicator.arithmetic == "count":
        return q.select(fn.Count(value_))
    else:
        return q.select(fn.Max(value_))
Esempio n. 29
0
    def test_complicated(self):
        t = Table("abc")
        is_placebo = t.campaign_extra_info == "placebo"

        pixel_mobile_search = Case().when(
            is_placebo, t.action_fb_pixel_search + t.action_fb_mobile_search)
        unique_impressions = Case().when(is_placebo, t.unique_impressions)

        v = fn.Sum(pixel_mobile_search) / fn.Sum(
            unique_impressions) - 1.96 * fn.Sqrt(
                1 / fn.Sum(unique_impressions) * fn.Sum(pixel_mobile_search) /
                fn.Sum(unique_impressions) *
                (1 - fn.Sum(pixel_mobile_search) / fn.Sum(unique_impressions)))

        self.assertTrue(v.is_aggregate)
Esempio n. 30
0
    def test_groupby_with_case_uses_the_alias(self):
        q = (Query.from_(self.t).select(
            fn.Sum(self.t.foo).as_("bar"),
            Case().when(
                self.t.fname == "Tom",
                "It was Tom").else_("It was someone else.").as_("who_was_it"),
        ).groupby(Case().when(
            self.t.fname == "Tom",
            "It was Tom").else_("It was someone else.").as_("who_was_it")))

        self.assertEqual(
            'SELECT SUM("foo") "bar",'
            "CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
            "ELSE 'It was someone else.' END \"who_was_it\" "
            'FROM "abc" '
            'GROUP BY "who_was_it"',
            str(q),
        )