model.Sale.id,
func.row_number().over(
partition_by=model.Sale.store_id,
order_by=model.Sale.date.desc(),
).label("row_number"),
)).subquery("latest_sales")
latest_sales = (meta.session.query(model.Sale).join(
latest_sales_sq,
and_(
model.Sale.id == latest_sales_sq.c.id,
latest_sales_sq.c.row_number == 1,
)).order_by(model.Sale.id))
print("window functions to find the latest sale at each store")
print_table(latest_sales)
print("")
sales_by_store = (meta.session.query(
model.Sale.store_id.label("store_id"),
func.sum(model.Sale.amount).label("amount"),
).group_by(literal(1))).cte("stores")
sales_report = (meta.session.query(
func.left(sales_by_store.c.store_id, 4).label("region"),
func.sum(sales_by_store.c.amount).over(partition_by=func.left(
sales_by_store.c.store_id, 4)).label("sales_for_region"),
func.left(sales_by_store.c.store_id, 7).label("state"),
func.sum(sales_by_store.c.amount).over(partition_by=func.left(
sales_by_store.c.store_id, 7)).label("sales_for_state"),
sales_by_store.c.store_id.label("store"),
).group_by(literal(1))).cte("states")
sales_by_store = (session.query(
Sale.store_id.label("store_id"),
func.left(Sale.store_id, 7).label("state"),
func.left(Sale.store_id, 4).label("region"),
func.sum(Sale.amount).label("amount"),
).group_by(literal(1), literal(2), literal(3))).cte("stores")
sales_report = (session.query(
sales_by_region.c.region.label("region"),
sales_by_region.c.amount.label("sales_for_region"),
sales_by_state.c.state.label("state"),
sales_by_state.c.amount.label("sales_for_state"),
sales_by_store.c.store_id.label("store"),
sales_by_store.c.amount.label("sales_for_store"),
).select_from(sales_by_store).join(
sales_by_state,
sales_by_state.c.state == sales_by_store.c.state,
).join(
sales_by_region,
sales_by_region.c.region == sales_by_store.c.region,
).order_by(
sales_by_region.c.amount.desc(),
sales_by_state.c.amount.desc(),
sales_by_store.c.amount.desc(),
))
print("common table expressions to compute aggregates at multiple levels")
print_table(sales_report)
if __name__ == "__main__":
# some sort of generic record object with tags. imagine these to be tickets
# in an issue tracking system
meta.session.add_all([
model.Record(tags=["defect", "minor_change", "backend"]),
model.Record(tags=["defect", "major_change", "backend"]),
model.Record(tags=["enhancement", "ui/ux", "frontend"]),
])
meta.session.flush()
# filter the result with &&, the overlaps operator, which says "if either
# of these two arrays have any elements in common, include them in the
# output". works like checking against a non-empty set intersection.
records = (meta.session.query(model.Record).filter(
model.Record.tags.op("&&")(["defect", "backend"])))
print("filtering with &&")
print_table(records)
print("")
# unnest takes the array and expands it into rows. this allows you to, for
# example, calculate statistics on tags, find unique tags, etc.
counts = (meta.session.query(
func.unnest(model.Record.tags).label("tag"),
func.count().label("count"),
).group_by(literal(1)).order_by(literal(2).desc(), literal(1)))
print("unnest and counting tags")
print_table(counts)
lower="1970-01-01",
upper="9999-12-31",
),
name="Crestor",
description="20mg capsule",
),
])
session.flush()
drugs = (
session.query(DrugInfo)
.filter(DrugInfo.validity.op("@>")(func.current_date()))
)
print("filtering with range types, current time")
print_table(drugs)
print("")
drugs = (
session.query(DrugInfo)
.filter(DrugInfo.validity.op("@>")(datetime.date(2019, 1, 1)))
)
print("filtering with range types, explicit date")
print_table(drugs)
print("")
session.add_all([
DrugPrice(
id="1234567890",
validity=DateRange(