def test_row_number(self):
expr = an.RowNumber() \
.over(self.table_abc.foo) \
.orderby(self.table_abc.date)
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
'SELECT '
'ROW_NUMBER() '
'OVER(PARTITION BY "foo" ORDER BY "date") '
'FROM "abc"', str(q))
def select_count_group(self,
table: str,
field: Optional[str],
group: str,
distinct: bool = False,
start: Optional[date] = None,
stop: Optional[date] = None,
ascending: bool = True,
limit: Optional[int] = None) -> CountResult:
target = Table(table, schema=self.schema)
count_field = fn.Count(
Field(field, table=target) if field else target.date,
alias='count')
order = Order.asc if ascending else Order.desc
if distinct:
count_field = count_field.distinct()
group_field = Field(group, table=target)
query = Query.from_(target).select(target.date, group_field.as_('group'), count_field). \
groupby(target.date, group_field).orderby(target.date). \
orderby(count_field, order=order).orderby(group_field)
query = self.apply_dates(query, target, start, stop)
if limit is not None:
window = Query.from_(query).select(
query.date, query.group, query.count,
an.RowNumber(alias='row_number').over(query.date).orderby(
query.count, order=order).orderby(query.group))
query = Query.from_(window).select(window.date, window.group, window.count). \
where(window.row_number <= limit).orderby(window.date). \
orderby(window.count, order=order).orderby(window.group)
result = CountResult(table=table,
field=field,
distinct=distinct,
group=group,
ascending=ascending,
elements=[])
for current in self.run(query):
result.elements.append(
Count(date=date.fromordinal(current[0]),
group=current[1],
count=int(current[2])))
return result