sql - postgresql-多列的前5个值的大小以及值的总和

我正在寻找关于计算多个列的前5个值的最佳（最有效）方法的建议，我还需要计算另一列的和。
假设我有标题的数据：
（person，daydate，month，dailyqty，dailymax1，dailymax2），其中对于每个人的每一天，我都有总数量、最大数量（测量1）和最大数量（测量2）。
我要做的是为每个人计算每个月（1）每日数量之和，（2）每日最大值的前5个值（3）每日最大值的前5个值。可能一个月甚至没有5个日值，在这种情况下我希望返回null。
因为我是一个sql新手，我想不出没有join怎么做。
我知道dailyqty的总和将在组中的前5个值中重复-这没关系。
一些虚拟数据：

CREATE TABLE test (
    person varchar(50),
    daydate date,
    month integer,
    dailyqty double precision,
    dailymax1 double precision,
    dailymax2 double precision
);

INSERT INTO test(person, daydate, month, dailyqty, dailymax1, dailymax2)
VALUES
    ('A', '2015-01-01', 1, 5, 0.5, 4),
    ('A', '2015-01-02', 1, 8, 3, 4),
    ('A', '2015-01-03', 1, 7, 1, 3),
    ('A', '2015-01-04', 1, 1, 2, 2),
    ('A', '2015-01-05', 1, 9, 6, 8),
    ('A', '2015-01-06', 1, 7, 2.5, 7),
    ('A', '2015-01-07', 1, 2, 4, 7),
    ('A', '2015-01-08', 1, 5, 1, 3),
    ('B', '2015-01-01', 1, 20, 8, 1),
    ('B', '2015-01-02', 1, 22, 9, 2)

期望结果

谢谢！
一个

最佳答案

此查询复制问题中发布的所需结果：

SELECT xt1.person, xt1.month, xt1.monthlyqty, xt3.max1, xt4.max2
FROM (
  SELECT SUM(COALESCE(t.dailyqty, 0)) as monthlyqty, t.person, t.month
  FROM test t
  GROUP by t.person, t.month
) xt1
CROSS JOIN (
  VALUES (1), (2), (3), (4), (5)
) xt2
LEFT OUTER JOIN (
  SELECT t.person, t.month, t.dailymax1 as max1
  , ROW_NUMBER() OVER (PARTITION BY t.person, t.month ORDER BY t.dailymax1 DESC NULLS LAST) as colnumber
  FROM test t
) xt3 ON xt2.column1 = xt3.colnumber AND xt1.person = xt3.person AND xt1.month = xt3.month
LEFT OUTER JOIN (
  SELECT t.person, t.month, t.dailymax2 as max2
  , ROW_NUMBER() OVER (PARTITION BY t.person, t.month ORDER BY t.dailymax2 DESC NULLS LAST) as colnumber
  FROM test t
) xt4 ON xt2.column1 = xt4.colnumber AND xt1.person = xt4.person AND xt1.month = xt4.month;

需要考虑的一些事情可能会改变查询。。。首先，您可以考虑dailyqty、dailymax1和dailymax2列是否真的可以为空（就像在表定义中一样）。如果不是，可以将COALESCE(t.dailyqty, 0)简化为t.dailyqty，将两个DESC NULLS LAST简化为DESC。
其次，您可以考虑将CROSS JOIN替换为xt2调用的连接，例如：generate_series，然后将CROSS JOIN generate_series (1, 5) xt2外观替换为xt2.column1。我不确定哪种方法更有效，也许两种方法都做了类似的事情，但是如果有显著的差异，就值得用真实的数据来检查。
最后，你说你想计算每个人和每个月，但“月”可以指“月”列或“日”列中的月。我选择了第一个选项，因为它更容易编写：），但是修改了一些内容，查询可以适应另一列。

关于sql - postgresql-多列的前5个值的大小以及值的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49102504/