如何使用我下面描述的原始表和期望表来分析PostgreSQL上的数据

Question

我的数据是这样的：

价格

项目		年份
帽子	2020	15
帽子	2021	17
帽子	2022	19
笔	2020	3
笔	2021	2
笔	2022	3.3

只需使用滞后窗口函数，它允许您回顾前一行。由于这需要多次回顾操作，因此您可以在查询中定义并使用WINDOW定义。(见演示(

select item, year, price 
, lag(year)  over w  previous_year
, lag(price) over w  previous_price
, case when price > lag(price) over w  then 'Yes' 
when lag(price) over w   is null then null::text  
else 'No'
end higher_than_previous_year
from origin
window w as (partition by item order by year);  

您可以使用简单的JOINS和UNION来实现这一点(多种方法之一(：

查询：

SELECT 
a.item, 
a.year, 
a.price, 
NULL AS prev_year, 
NULL AS prev_price,
NULL AS higher_than_previous_year
FROM
(
Select *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Year) as rn
from t
) as a
WHERE rn=1
UNION
SELECT 
a.item, 
a.year, 
a.price, 
b.year as prev_year, 
b.price as prev_price,
CASE
WHEN a.price > b.price THEN 'yes'
ELSE 'No'
END AS higher_than_previous_year
FROM 
(
Select *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Year) as rn
from t
) as a
LEFT JOIN
(
Select *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Year) as rn
from t
) as b
ON a.item=b.item and a.rn=b.rn+1
WHERE a.rn>1

解释：

1. 首先创建一个ROW_NUM列以查找前几行-在使用FROM和JOIN的所有位置使用相同的表
2. UNION之上：获取带有1的行，因为它们很容易继续
3. UNION下方：自加入表以获取其prev值

请参阅db<gt；小提琴

如果您有访问权限，我建议创建intermediate tables，这样查询看起来更简单。请参阅fidd(创建中间表，以便查询易于理解和调试(

我之前在对@arun的解决方案的一条评论中说过，有一个额外的列叫做"category"。我发现这个查询效果最好。

SELECT *, 
CASE WHEN price > previous_price THEN 'yes'
WHEN price <= previous_price THEN 'no'
ELSE null
END AS is_current_year_price_higher
FROM
(
SELECT *,
LAG(year,1) OVER (PARTITION BY item, category ORDER BY item, year) AS previous_year,
LAG(price,1) OVER (PARTITION BY item, category ORDER BY item, year) AS previous_price
FROM table_name
) AS main