51工具盒子英文:
Using the SQL LAG() function
问题 {#heading}
我尝试在SQL中使用LAG函数来实现这个目标，但我不确定我是否在使用时出错了。
我尝试的是，当在LOCAL_ID列下看到NULL时，用上面的LOCAL_ID填充该NULL。
因此，每当在ORDER_IN_LOCAL_ID列下看到1，并且在GROUP_ID列下看到一个新的ID开始时，就用上面NULL上面的LOCAL_ID填充LOCAL_ID。
我已经修改了原始图像以显示一个更大的数据集。我认为我在LAG函数中的问题是它只会取直接上面的值，所以我的问题是当有许多NULL时，它只会取上面的NULL，而不是该组顶部的LOCAL_ID。
输出：
UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1 808 1 808 1
2 NULL 1 909 2
3 676 7 676 1
4 674 8 674 1
5 352 9 352 1
6 NULL 9 134 2
7 232 11 232 1
8 NULL 11 431 2
9 NULL 11 323 3
10 NULL 11 567 4
11 800 98 800 1
11 NULL 98 786 2
11 NULL 98 345 3
输出：
UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1 808 1 808 1
2 808 1 909 2
3 676 7 676 1
4 674 8 674 1
5 352 9 352 1
6 352 9 134 2
7 232 11 232 1
8 232 11 431 2
9 232 11 323 3
10 232 11 567 4
11 800 98 800 1
11 800 98 786 2
11 800 98 345 3
查询：
,CASE WHEN a.LOCAL_ID IS NULL THEN LAG(a.LOCAL_ID) OVER (
ORDER BY b.GROUP_ID
) ELSE a.LOCAL_ID END AS ANSWER
英文:
I'm trying to use the LAG function in SQL to attempt this but I'm unsure if I am using this incorrectly or not.
What I am trying to do is where you see a NULL under the LOCAL_ID column, fill that NULL with the LOCAL_ID above it.
So, each time you see a 1 under the column ORDER_IN_LOCAL_ID and the start of a new ID under the GROUP_ID column, then fill LOCAL_ID with what ever is in the LOCAL_ID above the NULL.
I have altered the original image to show a larger dataset. The issue I think I have with LAG function is that it it will only take the value directly above it, so my issue is when there are many NULLS, it will just end of taking the NULL above, rather than the LOCAL_ID at the top of that group.
Output :
UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1 808 1 808 1
2 NULL 1 909 2
3 676 7 676 1
4 674 8 674 1
5 352 9 352 1
6 NULL 9 134 2
7 232 11 232 1
8 NULL 11 431 2
9 NULL 11 323 3
10 NULL 11 567 4
11 800 98 800 1
11 NULL 98 786 2
11 NULL 98 345 3
Output :
UNIQUE_ID LOCAL_ID GROUP_ID LOCAL_STAY_ID ORDER_IN_LOCAL_ID
1 808 1 808 1
2 808 1 909 2
3 676 7 676 1
4 674 8 674 1
5 352 9 352 1
6 352 9 134 2
7 232 11 232 1
8 232 11 431 2
9 232 11 323 3
10 232 11 567 4
11 800 98 800 1
11 800 98 786 2
11 800 98 345 3
Query :
,CASE WHEN a.LOCAL_ID IS NULL THEN LAG(a.LOCAL_ID) OVER (
ORDER BY b.GROUP_ID
) ELSE a.LOCAL_ID END AS ANSWER
答案1 {#1}
得分: 1
CREATE TABLE your_table (
UNIQUE_ID INT,
LOCAL_ID INT,
GROUP_ID INT,
LOCAL_STAY_ID INT,
ORDER_IN_LOCAL_ID INT
);
INSERT INTO your_table (UNIQUE_ID, LOCAL_ID, GROUP_ID, LOCAL_STAY_ID, ORDER_IN_LOCAL_ID) VALUES
(1, 808, 1, 808, 1),
(2, NULL, 1, 909, 2),
(3, 676, 7, 676, 1),
(4, 674, 8, 674, 1),
(5, 352, 9, 352, 1),
(6, NULL, 9, 134, 2),
(7, 232, 11, 232, 1),
(8, NULL, 11, 431, 2),
(9, NULL, 11, 323, 3),
(10, NULL, 11, 567, 4),
(11, 800, 98, 800, 1),
(11, NULL, 98, 786, 1),
(11, NULL, 98, 345, 1);
CREATE TEMPORARY TABLE temp_table AS
WITH RECURSIVE fill_nulls AS (
SELECT
UNIQUE_ID,
CASE WHEN ORDER_IN_LOCAL_ID = 1 THEN LOCAL_ID ELSE NULL END AS LOCAL_ID
FROM your_table
WHERE ORDER_IN_LOCAL_ID = 1
UNION ALL
SELECT
t.UNIQUE_ID,
CASE WHEN t.ORDER_IN_LOCAL_ID = 1 THEN t.LOCAL_ID ELSE fn.LOCAL_ID END
FROM your_table t
JOIN fill_nulls fn ON t.UNIQUE_ID = fn.UNIQUE_ID + 1
)
SELECT * FROM fill_nulls;
UPDATE your_table AS orig
JOIN temp_table AS temp
ON orig.UNIQUE_ID = temp.UNIQUE_ID
SET orig.LOCAL_ID = temp.LOCAL_ID
WHERE orig.LOCAL_ID IS NULL;
SELECT * FROM your_table
| UNIQUE_ID | LOCAL_ID | GROUP_ID | LOCAL_STAY_ID | ORDER_IN_LOCAL_ID | |----------:|---------:|---------:|--------------:|------------------:| | 1 | 808 | 1 | 808 | 1 | | 2 | 808 | 1 | 909 | 2 | | 3 | 676 | 7 | 676 | 1 | | 4 | 674 | 8 | 674 | 1 | | 5 | 352 | 9 | 352 | 1 | | 6 | 352 | 9 | 134 | 2 | | 7 | 232 | 11 | 232 | 1 | | 8 | 232 | 11 | 431 | 2 | | 9 | 232 | 11 | 323 | 3 | | 10 | 232 | 11 | 567 | 4 | | 11 | 800 | 98 | 800 | 1 | | 11 | 800 | 98 | 786 | 1 | | 11 | 800 | 98 | 345 | 1 |
<details>
<summary>英文:</summary>
CREATE TABLE your_table (
UNIQUE_ID INT,
LOCAL_ID INT,
GROUP_ID INT,
LOCAL_STAY_ID INT,
ORDER_IN_LOCAL_ID INT
);
INSERT INTO your_table (UNIQUE_ID, LOCAL_ID, GROUP_ID, LOCAL_STAY_ID, ORDER_IN_LOCAL_ID) VALUES
(1, 808, 1, 808, 1),
(2, NULL, 1, 909, 2),
(3, 676, 7, 676, 1),
(4, 674, 8, 674, 1),
(5, 352, 9, 352, 1),
(6, NULL, 9, 134, 2),
(7, 232, 11, 232, 1),
(8, NULL, 11, 431, 2),
(9, NULL, 11, 323, 3),
(10, NULL, 11, 567, 4),
(11, 800, 98, 800, 1),
(11, NULL, 98, 786, 1),
(11, NULL, 98, 345, 1);
\> `status &gt; Records: 13 Duplicates: 0 Warnings: 0 &gt;`
CREATE TEMPORARY TABLE temp_table AS
WITH RECURSIVE fill_nulls AS (
SELECT
UNIQUE_ID,
CASE WHEN ORDER_IN_LOCAL_ID = 1 THEN LOCAL_ID ELSE NULL END AS LOCAL_ID
FROM your_table
WHERE ORDER_IN_LOCAL_ID = 1
UNION ALL
SELECT
t.UNIQUE_ID,
CASE WHEN t.ORDER_IN_LOCAL_ID = 1 THEN t.LOCAL_ID ELSE fn.LOCAL_ID END
FROM your_table t
JOIN fill_nulls fn ON t.UNIQUE_ID = fn.UNIQUE_ID + 1
)
SELECT * FROM fill_nulls;
> ``` status
> Records: 53 Duplicates: 0 Warnings: 0
> ```
UPDATE your_table AS orig
JOIN temp_table AS temp
ON orig.UNIQUE_ID = temp.UNIQUE_ID
SET orig.LOCAL_ID = temp.LOCAL_ID
WHERE orig.LOCAL_ID IS NULL;
> ``` status
> Rows matched: 7 Changed: 7 Warnings: 0
> ```
SELECT * FROM your_table
| UNIQUE\_ID | LOCAL\_ID | GROUP\_ID | LOCAL\_STAY\_ID | ORDER\_IN\_LOCAL\_ID |
|----------:|---------:|---------:|--------------:|------------------:|
| 1 | 808 | 1 | 808 | 1 |
| 2 | 808 | 1 | 909 | 2 |
| 3 | 676 | 7 | 676 | 1 |
| 4 | 674 | 8 | 674 | 1 |
| 5 | 352 | 9 | 352 | 1 |
| 6 | 352 | 9 | 134 | 2 |
| 7 | 232 | 11 | 232 | 1 |
| 8 | 232 | 11 | 431 | 2 |
| 9 | 232 | 11 | 323 | 3 |
| 10 | 232 | 11 | 567 | 4 |
| 11 | 800 | 98 | 800 | 1 |
| 11 | 800 | 98 | 786 | 1 |
| 11 | 800 | 98 | 345 | 1 |
https://dbfiddle.uk/nw2Zl6BH
\</details\>
