预测履行SQL查询 [英] Forcast fulfillment sql query
问题描述
我遇到了这样的要求:我需要编写sql查询，以根据仓库的排名和销售排名将库存从一个位置填充到另一个位置. 下面是方案: 我有一张桌子上有产品A的剩余数量(表1) 我有另一个表，该表具有产品A的必需数量(预测)(表2).
I have came across with the requirement where I need to write sql query to fill stock from one location to another based on their warehouse rank and sales rank. Below is the scenario: I have one table which is having surplus quantities of product A(table 1) I have another table which is having required quantities(forecast) of product A (table 2).
我需要根据其等级从(表1)A到(表2)B发送产品数量(对于A SOH_RNK，对于B SLS_RNK)
I need to send product quantities from (table 1) A to (table 2 )B according to their rank (for A SOH_RNK , for B SLS_RNK)
表1:
PROD_ID SOH_RNK QTY STORE_ID_A
A 1 30 30105
A 2 16 21168
A 3 10 21032
A 4 9 30118
A 9 6 30011
A 10 5 21190
A 13 2 21016
表2:
PROD_ID SLS_RNK Forcast_QTY STORE_ID_B
A 1 15 21005
A 2 10 30019
A 3 11 21006
A 4 16 30001
A 5 11 21015
A 6 7 21004
预期输出:
Store_ID_B Store_id_A Transferred_Qty_from_A
21005 30105 15
30019 30105 10
21006 30105 5
21006 21168 6
30001 30105 0
30001 21168 10
30001 21032 6
21015 30105 0
21015 21168 0
21015 21032 4
21015 30118 7
21004 30105 0
21004 21168 0
21004 21032 0
21004 30118 2
21004 30011 5
到目前为止，我已经尝试过进行交叉连接，但是并没有太大帮助.我试图避免采用程序方法，因为它会逐行记录，而且我有2000万这样的记录.
So far I have tried with the making cross join but didnt help much . I am trying to avoid procedural approach as it will be row by row and I have records with 20 million of such .
推荐答案
该想法是确定两个表中的数量范围，然后根据这些范围将两个表连接起来，以获得从源商店发送到产品目录的产品数量.目的地商店.
这是一个查询示例，它计算第一个表的范围(第二个表的查询与此相似):
The idea is to determine ranges of quantities in both tables, then join both tables based on these ranges to obtain a quantity of products sent from the source store to the destination store.
This is an example of query that calculates ranges for the first table (the query for the second table is similar to this one):
select t1.*,
coalesce(sum( qty )
over ( order by soh_rnk
range between unbounded preceding and 1 preceding),
0) As range_start,
sum( qty ) over ( order by soh_rnk ) As range_end
from table1 t1
PROD_ID |SOH_RNK |QTY |STORE_ID_A |RANGE_START |RANGE_END |
--------|--------|----|-----------|------------|----------|
A |1 |30 |30105 |0 |30 |
A |2 |16 |21168 |30 |46 |
A |3 |10 |21032 |46 |56 |
A |4 |9 |30118 |56 |65 |
A |9 |6 |30011 |65 |71 |
A |10 |5 |21190 |71 |76 |
A |13 |2 |21016 |76 |78 |
现在，当两个表中的两个数据范围相互重叠时，我们就可以连接两个表中的记录.
可以在此处找到检查两个范围是否重叠的公式:
Now we can join records from both tables when two data ranges from both tables overlaps each other.
A formula for checking if two ranges overlap can be found here: http://www.soliantconsulting.com/blog/2016/03/determining-if-two-date-ranges-overlap
It is: NOT (EndA <= StartB or StartA >= EndB)
The final query:
WITH t1 AS (
select t1.*,
coalesce(sum( qty ) over ( order by soh_rnk range between unbounded preceding and 1 preceding),0) As range_start,
sum( qty ) over ( order by soh_rnk ) As range_end
from table1 t1
),
t2 As (
select t2.*,
coalesce(sum( forecast_qty ) over ( order by sls_rnk range between unbounded preceding and 1 preceding),0) As range_start,
sum( forecast_qty ) over ( order by sls_rnk ) As range_end
from table2 t2
)
SELECT
t1.store_id_a,
t2.store_id_b,
least( t1.range_end, t2.range_end ) - greatest( t1.range_start, t2.range_start ) As Transferred_Qty_from_A
FROM t1
join t2
-- NOT (EndA <= StartB or StartA >= EndB)
on not ( t1.range_end <= t2.range_start OR t1.range_start >= t2.range_end)
and t1.prod_id = t2.prod_id
order by soh_rnk, sls_rnk
;
此查询的结果是:
STORE_ID_A |STORE_ID_B |TRANSFERRED_QTY_FROM_A |
-----------|-----------|-----------------------|
30105 |21005 |15 |
30105 |30019 |10 |
30105 |21006 |5 |
21168 |21006 |6 |
21168 |30001 |10 |
21032 |30001 |6 |
21032 |21015 |4 |
30118 |21015 |7 |
30118 |21004 |2 |
30011 |21004 |5 |
不幸的是，我不知道如何在结果集中包含数量为0的记录(当没有产品从第一家商店转移到第二家商店时).
Unluckily I have no idea how to include records with 0 quantity in the resultset (when none of products were transfered from 1st to 2nd store).
这篇关于预测履行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
