我在下面的查询中为我提供了三列数据- type
,amount
以及total
上周使用week_number
列的数据。
select type,
case
WHEN (type = 'PROC1' AND contractdomicilecode = 'UIT') THEN 450
WHEN (type = 'PROC1' AND contractdomicilecode = 'KJH') THEN 900
WHEN (type = 'PROC2' AND contractdomicilecode = 'LOP') THEN 8840
WHEN (type = 'PROC2' AND contractdomicilecode = 'AWE') THEN 1490
WHEN (type = 'PROC3' AND contractdomicilecode = 'MNH') THEN 1600
WHEN (type = 'PROC3' AND contractdomicilecode = 'LKP') THEN 1900
END as amount,
total
from xyz.orders pa
join
(select clientid as clientid, max(version) as version
from xyz.orders where consumerid IN (select distinct entity_id from abc.items
where week_number = extract(week from current_date) - 1
and item_type like '%Ionize - Data%' )
and createdfor ='BLOCK'
and holder='RELAY_FUTURES'
group by clientid) pb on
pa.clientid = pb.clientid and pa.version = pb.version;
下面是我上面查询时返回的输出:
type amount total
---------------------------
PROC1 450 1768
PROC1 900 123
PROC1 450 456
PROC2 8840 99897
PROC2 1490 2223
PROC2 8840 9876
PROC3 1900 23456
PROC3 1600 12498
PROC3 1600 28756
问题陈述
现在,我试图找出一种方法来获取过去6周(不包括当前周)的数据。基本上,我想在过去6周内针对每种类型获取amount
,total
如下所示。
week type amount total
----------------------------------
46 PROC1 450 1768
46 PROC1 900 123
46 PROC1 450 456
46 PROC2 8840 99897
46 PROC2 1490 2223
46 PROC2 8840 9876
46 PROC3 1900 23456
46 PROC3 1600 12498
46 PROC3 1600 28756
45 PROC1 450 1768
45 PROC1 900 123
45 PROC1 450 456
45 PROC2 8840 99897
45 PROC2 1490 2223
45 PROC2 8840 9876
45 PROC3 1900 23456
45 PROC3 1600 12498
45 PROC3 1600 28756
44 PROC1 450 1768
44 PROC1 900 123
44 PROC1 450 456
44 PROC2 8840 99897
44 PROC2 1490 2223
44 PROC2 8840 9876
44 PROC3 1900 23456
44 PROC3 1600 12498
44 PROC3 1600 28756
43 PROC1 450 1768
43 PROC1 900 123
43 PROC1 450 456
43 PROC2 8840 99897
43 PROC2 1490 2223
43 PROC2 8840 9876
43 PROC3 1900 23456
43 PROC3 1600 12498
43 PROC3 1600 28756
42 PROC1 450 1768
42 PROC1 900 123
42 PROC1 450 456
42 PROC2 8840 99897
42 PROC2 1490 2223
42 PROC2 8840 9876
42 PROC3 1900 23456
42 PROC3 1600 12498
42 PROC3 1600 28756
41 PROC1 450 1768
41 PROC1 900 123
41 PROC1 450 456
41 PROC2 8840 99897
41 PROC2 1490 2223
41 PROC2 8840 9876
41 PROC3 1900 23456
41 PROC3 1600 12498
41 PROC3 1600 28756
这有可能吗?
select
如果表中current_date
存在该列,则可以尝试将以下代码添加到查询中,紧接在第1行中的第一行之后xyz.orders
:
extract(week from current_date) as week,
为了获得过去的星期数(在您的情况下为6周),将在以下行之后使用过滤器from xyz.orders pa
:
where datediff(week, extract(week, current_date)-6, extract(week, current_date)+1) = 6
过滤器可能需要稍作调整,但是当您看到输出时,您应该能够找到需要调整的内容。
DATEDIFF
MS docs上面的过滤器中使用的函数中的一些详细信息可能是有用的参考。
经过上述更改,以下是有关从原始代码添加的详细信息的更新查询和注释。
select
extract(week from current_date) as week, -- added code
type,
case
WHEN (type = 'PROC1' AND contractdomicilecode = 'UIT') THEN 450
WHEN (type = 'PROC1' AND contractdomicilecode = 'KJH') THEN 900
WHEN (type = 'PROC2' AND contractdomicilecode = 'LOP') THEN 8840
WHEN (type = 'PROC2' AND contractdomicilecode = 'AWE') THEN 1490
WHEN (type = 'PROC3' AND contractdomicilecode = 'MNH') THEN 1600
WHEN (type = 'PROC3' AND contractdomicilecode = 'LKP') THEN 1900
END as amount,
total
from xyz.orders pa
where datediff(week, extract(week, current_date)-6, extract(week, current_date)+1) = 6 -- added code
join
(select clientid as clientid, max(version) as version
from xyz.orders where consumerid IN (select distinct entity_id from abc.items
where week_number = extract(week from current_date) - 1
and item_type like '%Ionize - Data%' )
and createdfor ='BLOCK'
and holder='RELAY_FUTURES'
group by clientid) pb on
pa.clientid = pb.clientid and pa.version = pb.version;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句