問題描述

我有一個對行進行分組的查詢.產(chǎn)品 ID 相同但日期和數(shù)量不同的多行被分組，以便我可以計算數(shù)量、平均價格等的總和.

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.

我現(xiàn)在需要添加上次銷售的日期和相應(yīng)的數(shù)量.日期加了MAX(date)，怎么加對應(yīng)的數(shù)量?

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

原始樣本數(shù)據(jù):

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

預(yù)期結(jié)果:

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

推薦答案

你可以使用窗口函數(shù):

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

這篇關(guān)于SQL - 選擇分組行的一個實例的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！