英文:
How to use LAG to shift a column value down one partition?
问题
看一下这个示例表格：
| 名称 | 地点 | 日期 | 季度 | 成本 |
|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 |
| ABC | XYZ | 02-01-2021 | 1 | 20 |
| ABC | XYZ | 03-01-2021 | 1 | 5 |
| ABC | XYZ | 04-01-2021 | 2 | 80 |
从这个表格中，我创建了一个按名称、地点和季度分区的“RUNNING_TOTAL”列。
| 名称 | 地点 | 日期 | 季度 | 成本 | 累计总额 |
|---|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 | 55 |
| ABC | XYZ | 02-01-2021 | 1 | 20 | 55 |
| ABC | XYZ | 03-01-2021 | 1 | 5 | 55 |
| ABC | XYZ | 04-01-2021 | 2 | 80 | 80 |
现在我想将该分组的“累计总额”列下移，以获得一种“前期累计总额”值，如下所示：
| 名称 | 地点 | 日期 | 季度 | 成本 | 累计总额 | 前期RT |
|---|---|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 | 30 | NULL |
| ABC | XYZ | 02-01-2021 | 1 | 20 | 50 | NULL |
| ABC | XYZ | 03-01-2021 | 1 | 5 | 55 | NULL |
| ABC | XYZ | 04-01-2021 | 2 | 80 | 80 | 55 |
我尝试按照与“RUNNING_TOTAL”列相同的方式分区：
LAG(RUNNING_TOTAL) OVER (PARTITION BY 名称, 地点, 季度 ORDER BY 名称, 地点, 季度) AS 前期RT
正如您可以想象的那样，这并不起作用，我得到的结果是：
| 名称 | 地点 | 日期 | 季度 | 成本 | 累计总额 | 前期RT |
|---|---|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 | 30 | NULL |
| ABC | XYZ | 02-01-2021 | 1 | 20 | 50 | 55 |
| ABC | XYZ | 03-01-2021 | 1 | 5 | 55 | 55 |
| ABC | XYZ | 04-01-2021 | 2 | 80 | 80 | 55 |
非常感谢您的帮助，如何实现所需的结果。
英文:
Look at the example table:
| Name | Places | Date | Quarter | Cost |
|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 |
| ABC | XYZ | 02-01-2021 | 1 | 20 |
| ABC | XYZ | 03-01-2021 | 1 | 5 |
| ABC | XYZ | 04-01-2021 | 2 | 80 |
From this table, I created a RUNNING_TOTAL column partitioned by Name, Places and Quarter.
| Name | Places | Date | Quarter | Cost | Running Total |
|---|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 | 55 |
| ABC | XYZ | 02-01-2021 | 1 | 20 | 55 |
| ABC | XYZ | 03-01-2021 | 1 | 5 | 55 |
| ABC | XYZ | 04-01-2021 | 2 | 80 | 80 |
What I'm trying to do now is shift the running total column down for that group, to have sort of a "Previous Running Total" value, as below:
| Name | Places | Date | Quarter | Cost | Running Total | Previous RT |
|---|---|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 | 30 | NULL |
| ABC | XYZ | 02-01-2021 | 1 | 20 | 50 | NULL |
| ABC | XYZ | 03-01-2021 | 1 | 5 | 55 | NULL |
| ABC | XYZ | 04-01-2021 | 2 | 80 | 80 | 55 |
I've tried to partition the exact same way I partition my RUNNING_TOTAL column:
LAG(RUNNING_TOTAL) OVER (PARTITION BY NAME, PLACES, QUARTER ORDER BY NAME, PLACES, QUARTER) AS PREVIOUS_RT
As you can imagine, this is not working and the result I'm getting is:
| Name | Places | Date | Quarter | Cost | Running Total | Previous RT |
|---|---|---|---|---|---|---|
| ABC | XYZ | 01-01-2021 | 1 | 30 | 30 | NULL |
| ABC | XYZ | 02-01-2021 | 1 | 20 | 50 | 55 |
| ABC | XYZ | 03-01-2021 | 1 | 5 | 55 | 55 |
| ABC | XYZ | 04-01-2021 | 2 | 80 | 80 | 55 |
Any help on how to achieve the desired result is greatly appreciated!
答案1
得分: 1
使用分析函数`SUM`和`RANGE`窗口：
```lang-sql
选择 t.*，
SUM(cost) OVER (
PARTITION BY name, places, quarter
ORDER BY Date_column
) AS q_total,
SUM(cost) OVER (
PARTITION BY name, places
ORDER BY quarter
RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS prev_q_total
FROM table_name t
对于示例数据：
创建表 table_name (Name, Places, Date_Column, Quarter, Cost) AS
SELECT 'ABC', 'XYZ', DATE '2021-01-01', 1, 30 FROM DUAL UNION ALL
SELECT 'ABC', 'XYZ', DATE '2021-01-02', 1, 20 FROM DUAL UNION ALL
SELECT 'ABC', 'XYZ', DATE '2021-01-03', 1, 5 FROM DUAL UNION ALL
SELECT 'ABC', 'XYZ', DATE '2021-01-04', 2, 80 FROM DUAL;
输出：
| NAME | PLACES | DATE_COLUMN | QUARTER | COST | Q_TOTAL | PREV_Q_TOTAL |
|---|---|---|---|---|---|---|
| ABC | XYZ | 2021-01-01 00:00:00 | 1 | 30 | 30 | null |
| ABC | XYZ | 2021-01-02 00:00:00 | 1 | 20 | 50 | null |
| ABC | XYZ | 2021-01-03 00:00:00 | 1 | 5 | 55 | null |
| ABC | XYZ | 2021-01-04 00:00:00 | 2 | 80 | 80 | 55 |
<details>
<summary>英文:</summary>
Use the analytic `SUM` function with a `RANGE` window:
```lang-sql
SELECT t.*,
SUM(cost) OVER (
PARTITION BY name, places, quarter
ORDER BY Date_column
) AS q_total,
SUM(cost) OVER (
PARTITION BY name, places
ORDER BY quarter
RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS prev_q_total
FROM table_name t
Which, for the sample data:
CREATE TABLE table_name (Name, Places, Date_Column, Quarter, Cost) AS
SELECT 'ABC', 'XYZ', DATE '2021-01-01', 1, 30 FROM DUAL UNION ALL
SELECT 'ABC', 'XYZ', DATE '2021-01-02', 1, 20 FROM DUAL UNION ALL
SELECT 'ABC', 'XYZ', DATE '2021-01-03', 1, 5 FROM DUAL UNION ALL
SELECT 'ABC', 'XYZ', DATE '2021-01-04', 2, 80 FROM DUAL;
Outputs:
| NAME | PLACES | DATE_COLUMN | QUARTER | COST | Q_TOTAL | PREV_Q_TOTAL |
|---|---|---|---|---|---|---|
| ABC | XYZ | 2021-01-01 00:00:00 | 1 | 30 | 30 | null |
| ABC | XYZ | 2021-01-02 00:00:00 | 1 | 20 | 50 | null |
| ABC | XYZ | 2021-01-03 00:00:00 | 1 | 5 | 55 | null |
| ABC | XYZ | 2021-01-04 00:00:00 | 2 | 80 | 80 | 55 |
答案2
得分: 0
如果您希望之前的累计总额不考虑季度，就像您在示例中所示：
select name, places, dat, quarter, cost, running_total,
lag(running_total) over(partition by name, places order by dat) as prev_running_total
from (
select name, places, dat, quarter, cost, sum(cost) over(partition by name, places, quarter order by dat) as running_total
from data
)
英文:
If you want the previous running total to not take into account the quarter as you shown in your example:
select name, places, dat, quarter, cost, running_total,
lag(running_total) over(partition by name, places order by dat) as prev_running_total
from (
select name, places, dat, quarter, cost, sum(cost) over(partition by name, places, quarter order by dat) as running_total
from data
)
答案3
得分: 0
以下是已翻译的代码部分：
第一部分：
WITH -- Sample Data
tbl(A_NAME, PLACES, A_DATE, QUARTER, A_COST) AS
(
Select 'ABC', 'XYZ', To_Date('01-01-2021', 'mm-dd-yyyy'), 1, 30 From Dual Union All
Select 'ABC', 'XYZ', To_Date('02-01-2021', 'mm-dd-yyyy'), 1, 20 From Dual Union All
Select 'ABC', 'XYZ', To_Date('03-01-2021', 'mm-dd-yyyy'), 1, 5 From Dual Union All
Select 'ABC', 'XYZ', To_Date('04-01-2021', 'mm-dd-yyyy'), 2, 80 From Dual
)
M a i n S Q L :
SELECT A_NAME, PLACES, A_DATE, QUARTER, A_COST, Q_RUNING_TOTAL,
CASE WHEN ROW_NUMBER() OVER(Partition By A_NAME, PLACES, QUARTER Order By A_NAME, PLACES, QUARTER) = 1 And
Nvl(LAG(Q_RUNING_TOTAL) OVER(Order By A_NAME, PLACES, QUARTER), 0) != 0
THEN Nvl(LAG(Q_RUNING_TOTAL) OVER(Order By A_NAME, PLACES, QUARTER), 0)
END "PREV_Q_RT"
FROM ( Select A_NAME, PLACES, A_DATE, QUARTER, A_COST,
Sum(A_COST) OVER(Partition By A_NAME, PLACES, QUARTER Order By A_NAME, PLACES, QUARTER Rows Between Unbounded Preceding And Current Row) "Q_RUNING_TOTAL"
From tbl )
ORDER BY A_NAME, PLACES, A_DATE
R e s u l t :
A_NAME PLACES A_DATE QUARTER A_COST Q_RUNING_TOTAL PREV_Q_RT
------ ------ --------- ---------- ---------- -------------- ----------
ABC XYZ 01-JAN-21 1 30 30
ABC XYZ 01-FEB-21 1 20 50
ABC XYZ 01-MAR-21 1 5 55
ABC XYZ 01-APR-21 2 80 80 55
第二部分：
WITH -- Sample Data
tbl(A_NAME, PLACES, A_DATE, QUARTER, A_COST) AS
(
Select 'ABC', 'XYZ', To_Date('01-01-2021', 'mm-dd-yyyy'), 1, 30 From Dual Union All
Select 'ABC', 'XYZ', To_Date('02-01-2021', 'mm-dd-yyyy'), 1, 20 From Dual Union All
Select 'ABC', 'XYZ', To_Date('03-01-2021', 'mm-dd-yyyy'), 1, 5 From Dual Union All
--
Select 'ABC', 'XYZ', To_Date('04-01-2021', 'mm-dd-yyyy'), 2, 5 From Dual Union All
Select 'ABC', 'XYZ', To_Date('05-01-2021', 'mm-dd-yyyy'), 2, 35 From Dual Union All
Select 'ABC', 'XYZ', To_Date('06-01-2021', 'mm-dd-yyyy'), 2, 80 From Dual
)
M a i n S Q L :
SELECT t.A_NAME, t.PLACES, t.A_DATE, t.QUARTER, t.A_COST, t.Q_RUNING_TOTAL,
CASE WHEN (PREV_Q = To_Char(ADD_MONTHS(A_DATE, -3), 'Q/yyyy'))
THEN ( Select Sum(A_COST) From tbl WHERE A_NAME = t.A_NAME And PLACES = t.PLACES And To_Char(A_DATE, 'Q/yyyy') = t.PREV_Q )
END "PREV_Q_RT"
FROM ( Select A_NAME, PLACES, A_DATE, QUARTER, A_COST,
Sum(A_COST) OVER(Partition By A_NAME, PLACES, QUARTER Order By A_NAME, PLACES, QUARTER Rows Between Unbounded Preceding And Current Row) "Q_RUNING_TOTAL",
To_Char(ADD_MONTHS(A_DATE, -3), 'Q/yyyy') "PREV_Q"
From tbl ) t
ORDER BY A_NAME, PLACES, A_DATE
R e s u l t :
A_NAME PLACES A_DATE QUARTER A_COST Q_RUNING_TOTAL PREV_Q_RT
------ ------ --------- ---------- ---------- -------------- ----------
ABC XYZ 01-JAN-21 1 30 30
ABC XYZ 01-FEB-21 1 20 50
ABC XYZ 01-MAR-21 1 5 55
ABC XYZ 01-APR-21 2 5 5 55
ABC XYZ 01-MAY-21 2 35 40 55
ABC XYZ 01-JUN-21 2 80 120 55
希望这对您有所帮助。如果您有任何其他问题，不要犹豫，随时提问。
英文:
Try it like here:
WITH -- Sample Data
tbl(A_NAME, PLACES, A_DATE, QUARTER, A_COST) AS
(
Select 'ABC', 'XYZ', To_Date('01-01-2021', 'mm-dd-yyyy'), 1, 30 From Dual Union All
Select 'ABC', 'XYZ', To_Date('02-01-2021', 'mm-dd-yyyy'), 1, 20 From Dual Union All
Select 'ABC', 'XYZ', To_Date('03-01-2021', 'mm-dd-yyyy'), 1, 5 From Dual Union All
Select 'ABC', 'XYZ', To_Date('04-01-2021', 'mm-dd-yyyy'), 2, 80 From Dual
)
M a i n S Q L :
SELECT A_NAME, PLACES, A_DATE, QUARTER, A_COST, Q_RUNING_TOTAL,
CASE WHEN ROW_NUMBER() OVER(Partition By A_NAME, PLACES, QUARTER Order By A_NAME, PLACES, QUARTER) = 1 And
Nvl(LAG(Q_RUNING_TOTAL) OVER(Order By A_NAME, PLACES, QUARTER), 0) != 0
THEN Nvl(LAG(Q_RUNING_TOTAL) OVER(Order By A_NAME, PLACES, QUARTER), 0)
END "PREV_Q_RT"
FROM ( Select A_NAME, PLACES, A_DATE, QUARTER, A_COST,
Sum(A_COST) OVER(Partition By A_NAME, PLACES, QUARTER Order By A_NAME, PLACES, QUARTER Rows Between Unbounded Preceding And Current Row) "Q_RUNING_TOTAL"
From tbl )
ORDER BY A_NAME, PLACES, A_DATE
R e s u l t :
A_NAME PLACES A_DATE QUARTER A_COST Q_RUNING_TOTAL PREV_Q_RT
------ ------ --------- ---------- ---------- -------------- ----------
ABC XYZ 01-JAN-21 1 30 30
ABC XYZ 01-FEB-21 1 20 50
ABC XYZ 01-MAR-21 1 5 55
ABC XYZ 01-APR-21 2 80 80 55
The above code will show prrevious quorter total only in first row of the actual quarter. If there are more data and if you want the previous quarter in every row of the current one then see the sample data and code below:
WITH -- Sample Data
tbl(A_NAME, PLACES, A_DATE, QUARTER, A_COST) AS
(
Select 'ABC', 'XYZ', To_Date('01-01-2021', 'mm-dd-yyyy'), 1, 30 From Dual Union All
Select 'ABC', 'XYZ', To_Date('02-01-2021', 'mm-dd-yyyy'), 1, 20 From Dual Union All
Select 'ABC', 'XYZ', To_Date('03-01-2021', 'mm-dd-yyyy'), 1, 5 From Dual Union All
--
Select 'ABC', 'XYZ', To_Date('04-01-2021', 'mm-dd-yyyy'), 2, 5 From Dual Union All
Select 'ABC', 'XYZ', To_Date('05-01-2021', 'mm-dd-yyyy'), 2, 35 From Dual Union All
Select 'ABC', 'XYZ', To_Date('06-01-2021', 'mm-dd-yyyy'), 2, 80 From Dual
)
M a i n S Q L :
SELECT t.A_NAME, t.PLACES, t.A_DATE, t.QUARTER, t.A_COST, t.Q_RUNING_TOTAL,
CASE WHEN (PREV_Q = To_Char(ADD_MONTHS(A_DATE, -3), 'Q/yyyy'))
THEN ( Select Sum(A_COST) From tbl WHERE A_NAME = t.A_NAME And PLACES = t.PLACES And To_Char(A_DATE, 'Q/yyyy') = t.PREV_Q )
END "PREV_Q_RT"
FROM ( Select A_NAME, PLACES, A_DATE, QUARTER, A_COST,
Sum(A_COST) OVER(Partition By A_NAME, PLACES, QUARTER Order By A_NAME, PLACES, QUARTER Rows Between Unbounded Preceding And Current Row) "Q_RUNING_TOTAL",
To_Char(ADD_MONTHS(A_DATE, -3), 'Q/yyyy') "PREV_Q"
From tbl ) t
ORDER BY A_NAME, PLACES, A_DATE
R e s u l t :
A_NAME PLACES A_DATE QUARTER A_COST Q_RUNING_TOTAL PREV_Q_RT
------ ------ --------- ---------- ---------- -------------- ----------
ABC XYZ 01-JAN-21 1 30 30
ABC XYZ 01-FEB-21 1 20 50
ABC XYZ 01-MAR-21 1 5 55
ABC XYZ 01-APR-21 2 5 5 55
ABC XYZ 01-MAY-21 2 35 40 55
ABC XYZ 01-JUN-21 2 80 120 55
This one should work across years too (the first does not).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论