SQL --选择一个分组行的实例

Question

我有一个查询，其中行是分组的。对产品ID相同但日期和数量不同的多行进行分组，以便计算数量和平均价格等。

我现在需要添加上次销售的日期和相应的数量。我用MAX( date )添加了日期，但是如何添加相应的数量？

我使用。

示例查询：

SELECT id,
       SUM(quantity) as 'Total q',
       AVG(price) as 'Avg price',
       MAX(price) as 'Max price',
       MAX(date) as 'Last sale date'
FROM table
WHERE date > 2018
GROUP BY id

原始样本数据：

id     quantity    price     date
1      20          2.30      2018-6-2
1      10          2.40      2018-6-4
1      5           2.55      2018-6-10
2      15          12.50     2018-5-20
2      100         7.50      2018-6-1
2      50          10.00     2018-6-12

预期结果：

id    total q    avg price   max price   last sale    last q  last p
1     35         2.42        2.55        2018-6-10    5       2.55
2     165        10.00       12.50       2018-6-12    50      10.00

Answer 1

你可以用windowed functions

SELECT DISTINCT id,
       SUM(quantity) OVER(PARTITION BY id) as "Total q",
       AVG(price) OVER(PARTITION BY id) as "Avg price",
       MAX(price) OVER(PARTITION BY id) as "Max price",
       MAX(_date) OVER(PARTITION BY id) as "Last sale date",
       FIRST_VALUE(quantity) OVER(PARTITION BY id ORDER BY _date DESC) AS last_q,
       FIRST_VALUE(price) OVER(PARTITION BY id ORDER BY _date DESC) AS last_p
FROM tab
WHERE _date > '2018-01-01';

输出：

┌────┬─────────┬───────────┬───────────┬─────────────────────┬────────┬────────┐
│ id │ Total q │ Avg price │ Max price │   Last sale date    │ last_q │ last_p │
├────┼─────────┼───────────┼───────────┼─────────────────────┼────────┼────────┤
│  1 │      35 │  2.416666 │      2.55 │ 10/06/2018 00:00:00 │      5 │   2.55 │
│  2 │     165 │ 10.000000 │     12.50 │ 12/06/2018 00:00:00 │     50 │  10.00 │
└────┴─────────┴───────────┴───────────┴─────────────────────┴────────┴────────┘

DBFiddle演示

Answer 2

使用一个变量，您可以设置相应的数量，可能是类似的

DECLARE @LastSaleQty AS int

SELECT id, @LastSaleQty = SUM(quantity)
FROM table
WHERE date = MAX(date)
GROUP BY id

SELECT id,
    SUM(quantity) as 'Total q',
    AVG(price) as 'Avg price',
    MAX(price) as 'Max price',
    MAX(date) as 'Last sale date',
    @LastSaleQty As 'Last Sale Qty'
FROM table
WHERE date > 2018
GROUP BY id