LAG函数 方法 需要改造为 适配 SQL server 2008
---改造前：
select
area_id,
area_name,
year,
lag(cast(year as int ),1,'1970') over (partition by area_id order by year) previous_year,
year_money,
lag(year_money, 1, 0) over (partition by area_id order by year) previous_year_money
from (select
area_id,
area_name,
year,
sum(month_area_order_sale_money) as year_money
from dws_area_sale_analyse_month
WHERE dt = '2024-06-17'
group by area_id,area_name,year) t1
---改造后：
SELECT '2024-06-17' as dt,t1.area_id,
t1.area_name, t1.year,
CASE WHEN t1_prev.year IS NULL THEN '1970' ELSE t1_prev.year END AS previous_year,t1.year_money AS year_money,
CASE WHEN t1_prev.year_money IS NULL THEN 0 ELSE t1_prev.year_money END AS previous_year_money
FROM (select
area_id,
area_name,
year,
sum(month_area_order_sale_money) as year_money
from dws_area_sale_analyse_month
WHERE dt = '2024-06-17'
group by area_id,area_name,year) as t1
LEFT JOIN (select
area_id,
area_name,
year,
sum(month_area_order_sale_money) as year_money
from dws_area_sale_analyse_month
WHERE dt = '2024-06-17'
group by area_id,area_name,year) as t1_prev
ON t1.year > t1_prev.year AND t1.area_id = t1_prev.area_id
WHERE NOT EXISTS ( SELECT 1 FROM (select
area_id,
area_name,
year,
sum(month_area_order_sale_money) as year_money
from dws_area_sale_analyse_month
WHERE dt = '2024-06-17'
group by area_id,area_name,year) as t2 WHERE t2.area_id = t1.area_id AND t2.year > t1_prev.year AND t2.year < t1.year)
其中 重复的sql语句 可以抽取为 with方法
WITH year_sales AS (select
area_id,
area_name,
year,
sum(month_area_order_sale_money) as year_money
from dws_area_sale_analyse_month
WHERE dt = '2024-06-17'
group by area_id,area_name,year)
