sql-server - SQL Server 滞后/跨组领先/密集排名
问题描述
我希望找到关于如何填写领先和滞后值的答案。
输入:
BRAND Promo_Start VALUE
TESLA 2016-06-05 NULL
TESLA 2016-06-12 NULL
TESLA 2016-06-19 40000
TESLA 2016-06-26 75000
TESLA 2016-07-03 75000
TESLA 2016-07-10 NULL
我想要的输出是:
BRAND Promo_Start VALUE
TESLA 2016-06-05 40000
TESLA 2016-06-12 40000
TESLA 2016-06-19 40000
TESLA 2016-06-26 75000
TESLA 2016-07-03 75000
TESLA 2016-07-10 75000
我已经能够填写最后一个值,但是,我没有成功填写前两个值。
BRAND Promo_Start VALUE FILLED_VALUE
TESLA 2016-06-05 NULL NULL
TESLA 2016-06-12 NULL NULL
TESLA 2016-06-19 40000 40000
TESLA 2016-06-26 75000 75000
TESLA 2016-07-03 75000 75000
TESLA 2016-07-10 NULL 75000
使用此查询:
WITH help1 as (
SELECT *,
CASE WHEN [VALUE] IS NULL THEN 0 ELSE 1 END CHANGEINDICATOR
FROM #SOExample
)
, help2 as (
SELECT *, SUM(CHANGEINDICATOR) OVER (ORDER BY [Promo_Start]) RowGroup from help1
)
SELECT [BRAND],[Promo_Start],[VALUE],
CASE WHEN [VALUE] IS NOT NULL THEN [VALUE]
ELSE FIRST_VALUE([VALUE]) OVER (PARTITION BY RowGroup ORDER BY [Promo_Start])
END [FILLED_VALUE]
FROM help2
GO
表是使用以下方法创建的:
CREATE TABLE #SOExample
([BRAND] varchar(10),[Promo_Start] varchar(10), [VALUE] varchar(15))
;
GO
INSERT INTO #SOExample
([BRAND],[Promo_Start], [VALUE])
VALUES
('TESLA', '2016-06-05',NULL),
('TESLA', '2016-06-12',NULL),
('TESLA', '2016-06-19','40000'),
('TESLA', '2016-06-26','75000'),
('TESLA', '2016-07-03','75000'),
('TESLA', '2016-07-10',NULL)
;
GO
我认为这个问题可能类似于:LAG() / LEAD() of the next rank (Postgresql) 并且我已经查看了这个以创建一个标志,这是因为它似乎是一个类似的问题。
我还研究了 dense_rank 并使用了更改指示器(从 NULL 更改为下一行中的值,并将值更改为下一行中的 NULL)。
解决方案
有2 OUTER APPLY
,第一个获得下一个非空值,第二个获得上一个非空值。
SELECT *, NEW_VALUE = COALESCE ( so.VALUE, n.FILLED_VALUE, p.FILLED_VALUE)
FROM #SOExample so
OUTER APPLY
(
SELECT TOP 1 FILLED_VALUE = x.VALUE
FROM #SOExample x
WHERE x.BRAND = so.BRAND
AND x.Promo_Start >= so.Promo_Start
AND x.VALUE IS NOT NULL
ORDER BY x.Promo_Start
) n
OUTER APPLY
(
SELECT TOP 1 FILLED_VALUE = x.VALUE
FROM #SOExample x
WHERE x.BRAND = so.BRAND
AND x.Promo_Start <= so.Promo_Start
AND x.VALUE IS NOT NULL
ORDER BY x.Promo_Start DESC
) p
推荐阅读
- lua - 如何在一段时间内保持一个值
- kubernetes - 如何将 Istio-ingressgateway 类型从“LoadBalancer”更改为“ClusterIP”
- python - 501 无效请求
- angular - 错误:发生未处理的异常:对象原型可能只是一个对象或 null:从 Angular 7 迁移到 9 时未定义
- excel - 在工作簿中循环通过 Excel 工作表
- python - Streamlit 输入更改时的重置结果
- java - 使用二进制搜索查找旋转点,为什么要与第一个元素进行比较?
- vuetify.js - Vuetify:单击 v-tab 中的选项卡时更新 URL
- xml - XPath 后代属性过滤器包含
- go - golang:使 select statemet 具有确定性?