mysql - 对 SUM 销售额的 SQL 查询，但仅对给定 GROUP 的 SUM 手头上的最新数据

Question

我在弄清楚如何以各种方式对表格进行 GROUP 时遇到一些问题 SUMing 售出的单位数量，但仅对每个 GROUP 中的每个项目的最新手头单位求和。

这是一个示例数据集:http://www.sqlfiddle.com/#!2/3ff18/1

我需要能够以这样的方式执行 GROUP BY，即仅对每个组中的最新项目求和 On Hand 列。

我在使用 MAX(date) 的“自连接”方面取得了一些进展，但是在使用各种 GROUP BY 时我没有得到想要的结果。

这是我希望在给定 sqlfiddle.com 数据集的情况下看到的一些示例输出:

Category     Sold  On Hand
Electronics   500        0
Books         500        0
Other           0      100

Quarter  Category     Sold  On Hand
Q1       Electronics   400      100
Q1       Books         400      100
Q1       Other           0      100
Q2       Electronics   100        0
Q2       Books         100        0
Q2       Other           0      100

Month    Sold  On Hand 
January   300      800 
February  100      700 
March     200      500 
April     200      300 
May         0      300 <- This May entry isn't strickly necessary, but it would be nice
June      100      200 
July      100      100 <- This 100 units On Hand is from Item 987 that hasn't been sold

MAX(date) 方法让我感到困惑的一个方面是 GROUP BY month。如果您查看上面的表格，您会注意到我希望看到 7 月份有 100 个库存……也就是说，除了 1 月份添加的项目 987 之外，所有单元都已售出，但不卖。

一些注意事项:

• 这是在使用 MySQL，但如果 PostgreSQL 具有辅助此功能的窗口功能，它会愿意尝试使用 PostgreSQL。
• 鉴于目前有 150 万条记录，该解决方案的性能相当重要。并且可能会看到更多的人加入。

Answer 1

在 Postgres 中，你有 a variety of window functions可供选择。

您有 DISTINCT ON 来选择每组列中 n 最大的行:
Select first row in each GROUP BY group?

并且您具有 date / time algebra 的函数和 formatting (其中大部分也在 MySQL 中)。所以冗余存储月份和季度是没有意义的。只会让你的 table 膨胀并减慢你的速度。我相应地调整了您的表格布局。请参阅下面的 fiddle 。

使用这个相应简化的 Postgres 表:

CREATE TABLE tbl (
   item int
  ,on_hand int
  ,sold int
  ,thedate date
  ,category text
);

演示 EXTRACT() & to_char():

SELECT EXTRACT(quarter FROM thedate)::int AS quarter_int
     , EXTRACT(month   FROM thedate)::int AS month_int
     , to_char(thedate, '"Q"Q')  AS quarter_text
     , to_char(thedate, 'Month') AS month_text
FROM   tbl
LIMIT 1;

仅获取每个(项目、月份)的最新行:

SELECT DISTINCT ON (item, date_trunc('month', thedate))
       *
FROM   tbl
ORDER  BY item, date_trunc('month', thedate), thedate DESC;

每个类别的总数:

SELECT category, sum(sold) AS sold, min(on_hand) AS on_hand
FROM  (
   SELECT category, sold
        , first_value(on_hand) OVER (PARTITION BY item
                                     ORDER BY thedate DESC) AS on_hand
   FROM tbl
   ) sub
GROUP  BY 1
ORDER  BY 1;

类别和月份相同:

SELECT category, to_char(month, 'YYYY-Mon') AS month
     , sum(sold) AS sold, min(on_hand) AS on_hand
FROM  (
   SELECT category, date_trunc('month', thedate) AS month, sold
        , first_value(on_hand) OVER (PARTITION BY item, date_trunc('month', thedate)
                                     ORDER BY thedate DESC) AS on_hand
   FROM tbl
   ) sub
GROUP  BY 1, sub.month
ORDER  BY 1, sub.month;

SQL Fiddle demo.