随着时间的推移进行计数和分组

Question

我需要在 PostgreSQL 中按天、周、月等创建销售报告。我有以下表格设置：

tbl_products:
    id INT
    name VARCHAR

tbl_purchase_order:
    id INT
    order_timestamp TIMESTAMP

tbl_purchase_order_items:
    id INT
    product_id INT (FK to tbl_products.id)
    order_id (FK to tbl_purchase_order.id)

我需要创建一个 SQL 查询，返回给定时间范围内购买给定产品的次数。也就是说，我需要查询给定产品 ID 在特定月份、日期、年份等中出现在采购订单项目中的次数。在之前的问题中，我学习了如何使用 date_trunc() 将我的 TIMESTAMP 列截断为我关心的那段时间。现在我面临着如何正确执行 COUNT 和 GROUP BY 的问题。

我已经尝试使用 COUNT(XXX) 和 GROUP BY XXX 的各种组合进行多次查询，但似乎从未达到我的预期。有人可以指导我如何构建这个查询吗？我更像是一名 Java 开发人员，所以我仍在加快 SQL 查询的速度。感谢您提供的任何帮助。

Answer 1

每年计数：

SELECT oi.product_id, 
       extract(year from po.order_timestamp) as order_year
       count(*)
FROM purchase_order_items oi
   JOIN purchase_order po ON po.id = oi.order_id
GROUP BY extract(year from po.order_timestamp)

每月计数：

SELECT oi.product_id, 
       extract(month from po.order_timestamp) as order_month
       extract(year from po.order_timestamp) as order_year
       count(*)
FROM purchase_order_items oi
   JOIN purchase_order po ON po.id = oi.order_id
GROUP BY extract(year from po.order_timestamp), 
         extract(month from po.order_timestamp)

Answer 2

请参阅 postgres 日期时间函数 http://www.postgresql.org/docs/ 8.1/static/functions-datetime.html

我建议您使用提取函数，将年、月和日拆分为结果集中的离散列，然后根据您的要求进行分组。