SQL - 选择分组行的一个实例 [英] SQL - Select one instance of grouped rows

问题描述

我有一个对行进行分组的查询.产品 ID 相同但日期和数量不同的多行被分组，以便我可以计算数量、平均价格等的总和.

I have a query where rows are grouped. Multiple rows where the product ID are the same but where date and quantity differs, are grouped so I can calculate the sum of quantities, average prices, etc.

我现在需要添加上次销售的日期和相应的数量.日期加了MAX(date)，怎么加对应的数量?

I now need to add the date when the last sale occurred and the corresponding quantity. I added the date with MAX(date), but how do I add the corresponding quantity?

我使用 MS SQL Server Management Studio.

I use MS SQL Server Management Studio.

示例查询:

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

推荐答案

你可以使用窗口函数:

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 演示

这篇关于SQL - 选择分组行的一个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！