如何在BQL中垂直翻转数据?

Question

Title.例如，我有以下数据：

密钥

1 密钥 2 成本Qty_LIFO日期    红A 2 19 1/4/2018 红 A 3 18 1/3/2018 红 C 4 7 1/2/2018 红 A 5 16 1/1/2018    蓝光B 21 91 1/4/2018 蓝光B 31 81 1/3/2018 蓝光D 41 70 2018/1/2 Blu D 51 60 1/1/2018

目标是将数据转换为如下所示。翻转数量列，同时还要考虑键/类别

密钥

1 密钥 2 成本Qty_FIFO日期    红 A 2 16 1/4/2018 红 A 3 18 1/3/2018 红 C 4 7 1/2/2018 红 A 5 19 1/1/2018    蓝光B 21 81 1/4/2018 蓝光B 31 91 1/3/2018 蓝光D 41 60 2018/1/2 Blu D 51 70 1/1/2018

或类似这样(Qty_FIFO被翻转并添加到顶部的第一个示例中(：

密钥

1 密钥 2 成本Qty_LIFO Qty_FIFO日期    红A 2 19 16 2018/1/4 红 A 3 18 18 2018/1/3 红 C 4 7 7 1/2/2018 红 A 5 16 19 1/1/2018    蓝光B 21 91 81 1/4/2018 蓝光B 31 81 91 1/3/2018 蓝光D 41 70 60 1/2/2018 Blu D 51 60 70 1/1/2018

这样做的目的是计算后进先出和先进先出成本。

我需要获取Qty_LIFO列(按日期降序排序(，垂直翻转它(因此数据变为日期 ASC(，然后将其重新添加到表中，而不更改成本列的排序。

基本上，我需要将最新的成本数据与最旧的数量数据配对，然后从那里继续。

Answer 1

这是一个黑客，只有在您有权访问row_number()时才有效

CREATE TABLE existing_qry(
Key1     VARCHAR(3) NOT NULL
,Key2     VARCHAR(1) NOT NULL
,Cost     INTEGER  NOT NULL
,Qty_LIFO INTEGER  NOT NULL
,Date     DATE  NOT NULL
);
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Red','A',2,19,'1/4/2018');
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Red','A',3,18,'1/3/2018');
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Red','C',4,7,'1/2/2018');
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Red','A',5,16,'1/1/2018');
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Blu','B',21,91,'1/4/2018');
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Blu','B',31,81,'1/3/2018');
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Blu','D',41,70,'1/2/2018');
INSERT INTO existing_qry(Key1,Key2,Cost,Qty_LIFO,Date) VALUES ('Blu','D',51,60,'1/1/2018');
with cte as (
select 
* 
, row_number() over(partition by key1 order by date ASC) rn_asc
, row_number() over(partition by key1 order by date DESC) rn_desc
from existing_qry
)
select
t.Key1, t.Key2, t.Cost, t.Qty_LIFO, flip.Qty_LIFO as Qty_FIFO, t.Date, t.rn_asc, t.rn_desc
from cte as t
inner join cte as flip on t.key1 = flip.key1 and t.rn_asc = flip.rn_desc

它以相反的日期顺序为每行计算 2 个数字，然后通过自连接要求这些数字相等来对齐行。这会产生反转后进先出数字(或"翻转"该列(的影响。

Key1   Key2   Cost   Qty_LIFO   Qty_FIFO          Date           rn_asc   rn_desc  
---- ------ ------ ------ ---------- ---------- --------------------- -------- --------- 
1   Blu    B        21         91         60   04.01.2018 00:00:00        4         1  
2   Blu    B        31         81         70   03.01.2018 00:00:00        3         2  
3   Blu    D        41         70         81   02.01.2018 00:00:00        2         3  
4   Blu    D        51         60         91   01.01.2018 00:00:00        1         4  
5   Red    A         2         19         16   04.01.2018 00:00:00        4         1  
6   Red    A         3         18          7   03.01.2018 00:00:00        3         2  
7   Red    C         4          7         18   02.01.2018 00:00:00        2         3  
8   Red    A         5         16         19   01.01.2018 00:00:00        1         4  

https://rextester.com/LQBVD29253