DB-小提琴
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
event_date DATE,
campaign VARCHAR(255),
sales_volume VARCHAR(255)
);
INSERT INTO sales
(event_date, campaign, sales_volume)
VALUES
('2020-01-01', 'C001', '125'),
('2020-01-02', 'C001', '300'),
('2020-01-07', 'C001', '700'),
('2020-01-12', 'C001', '900'),
('2020-01-08', 'C002', '600'),
('2020-01-10', 'C002', '465'),
('2020-02-15', 'C002', '570'),
('2020-03-22', 'C002', '780'),
('2020-02-01', 'C003', '150'),
('2020-03-17', 'C003', '325'),
('2020-03-18', 'C003', '482'),
('2020-04-19', 'C003', '323');
預期結果：
campaign | event_date |
----------|-----------------|---
C001 | 2020-01-07 |
C001 | 2020-01-12 |
----------|-----------------|---
C002 | 2020-02-15 |
C002 | 2020-03-22 |
----------|-----------------|---
C003 | 2020-03-18 |
C003 | 2020-04-19 |
在結果中，我想為每個廣告系列提取最新的和第二個最新的。
到目前為止，我可以通過此查詢獲得最新資訊：event_date
event_date
SELECT
campaign AS campaign,
MAX(s.event_date) AS event_date
FROM sales s
GROUP BY 1
ORDER BY 1;
我如何需要修改查詢以獲得第二個最新 event_date的？
uj5u.com熱心網友回復：
您可以使用視窗函式來實作這一點
select *
from (
select campaign,
event_date,
sales_volume,
dense_rank() over (partition by campaign order by event_date desc) as rnk
from sales
) t
where rnk <= 2
在線示例
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422665.html
標籤：