通过比较 4 个不同的行来提取数据 [英] Extract Data by comparing 4 different rows
问题描述
表格数据如下，需要提取满足以下条件的记录
The Table data is as below, need to extract records that met the below conditions
此处值 = 值 2-值 1
Here Value = Value2-Value1
Value of two days back data should be > 2
Value of last day data is < 0
Value of next day data is < 4 and >0
Value of after next day data > 4
所有日期都是工作日，如果有任何日期在星期五，需要与第二天即...星期一进行比较.并且仅与替代日期进行比较
All the dates are weekdays,if any date falls on friday, need to compare with next day ie.. Monday. and comparision is with alternative days only
必须从下面输出.
1 4-1-2018 15 18
2 3-1-2018 3 0
-----------------------------------
code Date Value1 Value2
---------------------------------------
1 1-1-2018 13 14
1 2-1-2018 14 18
1 3-1-2018 15 11
1 4-1-2018 15 18
1 5-1-2018 15 18
1 6-1-2018 11 18
1 7-1-2018 15 18
2 1-1-2019 1 3
2 2-1-2018 2 5
2 3-1-2018 3 0
2 4-1-2018 3 7
2 5-1-2018 3 4
2 6-1-2018 3 9
2 7-1-2018 3 7
我在比较多行时非常困惑，非常感谢任何帮助.
I am pretty much confused at comparing multiple rows, any help is greatly appreciated.
推荐答案
从 v2012 开始，我们有 支持 LAG() 和 LEAD().试试这个:
Starting with v2012 we have support for LAG() and LEAD(). Try this out:
SET DATEFORMAT dmy;
DECLARE @tbl TABLE(code INT,[Date] DATE,Value1 INT,Value2 INT);
INSERT INTO @tbl VALUES
(1,'1-1-2018',13,14)
,(1,'2-1-2018',14,18)
,(1,'3-1-2018',15,11)
,(1,'4-1-2018',15,18)
,(1,'5-1-2018',15,18)
,(1,'6-1-2018',11,18)
,(1,'7-1-2018',15,18)
,(2,'1-1-2019', 1, 3)
,(2,'2-1-2018', 2, 5)
,(2,'3-1-2018', 3, 0)
,(2,'4-1-2018', 3, 7)
,(2,'5-1-2018', 3, 4)
,(2,'6-1-2018', 3, 9)
,(2,'7-1-2018', 3, 7);
WITH cte AS
(
SELECT *
,LAG(Value2-Value1,2) OVER(PARTITION BY code ORDER BY [Date]) TwoDaysBack
,LAG(Value2-Value1,1) OVER(PARTITION BY code ORDER BY [Date]) Yesterday
,LEAD(Value2-Value1,1) OVER(PARTITION BY code ORDER BY [Date]) tomorrow
,LEAD(Value2-Value1,2) OVER(PARTITION BY code ORDER BY [Date]) TwoDaysAhead
FROM @tbl
)
SELECT *
FROM cte;
我不太明白，你想如何在过滤器中使用这些值来获得预期的输出.如果您需要这方面的帮助，请回来...
I do not really understand, how you want to use these values in a filter to get the expected output. If you need help with this, just come back...
结果
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| code | Date | Value1 | Value2 | TwoDaysBack | Yesterday | tomorrow | TwoDaysAhead |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 1 | 2018-01-01 | 13 | 14 | NULL | NULL | 4 | -4 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 1 | 2018-01-02 | 14 | 18 | NULL | 1 | -4 | 3 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 1 | 2018-01-03 | 15 | 11 | 1 | 4 | 3 | 3 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 1 | 2018-01-04 | 15 | 18 | 4 | -4 | 3 | 7 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 1 | 2018-01-05 | 15 | 18 | -4 | 3 | 7 | 3 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 1 | 2018-01-06 | 11 | 18 | 3 | 3 | 3 | NULL |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 1 | 2018-01-07 | 15 | 18 | 3 | 7 | NULL | NULL |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 2 | 2018-01-02 | 2 | 5 | NULL | NULL | -3 | 4 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 2 | 2018-01-03 | 3 | 0 | NULL | 3 | 4 | 1 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 2 | 2018-01-04 | 3 | 7 | 3 | -3 | 1 | 6 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 2 | 2018-01-05 | 3 | 4 | -3 | 4 | 6 | 4 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 2 | 2018-01-06 | 3 | 9 | 4 | 1 | 4 | 2 |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 2 | 2018-01-07 | 3 | 7 | 1 | 6 | 2 | NULL |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
| 2 | 2019-01-01 | 1 | 3 | 6 | 4 | NULL | NULL |
+------+------------+--------+--------+-------------+-----------+----------+--------------+
简单的想法:
LAG() 和 LEAD() 都接受所需值的参数，一秒，我们想要跳过的行数，以及作为第三个参数的默认值，您可能会指定一个默认值以避免结果中出现 NULL，当有范围内没有行.
Both, LAG() and LEAD() take an argument for the needed value, a second, how many rowswe want to skip, and as a third argument a default value you might specify to avoid NULLs in the result, when there is no row in scope.
OVER() 子句将告诉任何窗口函数，如果我们想将集合视为分组和排序顺序(否则系统将不知道什么是领先 或 滞后.
The OVER() clause will tell any windowing function if we want to think of the set as divided in groups and the sort order (otherwise the system would not know what is leading or lagging.
这篇关于通过比较 4 个不同的行来提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
