如何获取过去x周每种类型的数据? [英] How to get data for the past x weeks for each type?

问题描述

我在下面的查询中使用 week_number <为上一周提供了三列数据- type ， amount 和 total /code>列.

I have below query which gives me data with three columns - type, amount and total for previous week using week_number column.

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;

下面是我上面查询得到的结果:

Below is the output I get back as of now with above query:

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 ，如下所示.

Now I am trying to figure out a way by which I can get data for past 6 weeks (not including current week). Basically I want to get amount, total for each type for past 6 weeks as shown below.

 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

这有可能吗?

推荐答案

如果第列是第1行的第一个 select 之后，则可以尝试将以下代码添加到查询中 xyz.orders 表中存在current_date :

You may try adding the following code to your query, right after the first select in line 1, if the column current_date is present in the xyz.orders table:

extract(week from current_date) as week,

要获取过去的星期数(在您的情况下为6周)，将在xyz.orders pa 中的行之后使用过滤器:

To obtain the past number of weeks (6 in your case), a filter will be used after the line of from xyz.orders pa:

where datediff(week, extract(week, current_date)-6, extract(week, current_date)+1) = 6

过滤器可能需要稍作调整，但是当您看到输出时，您应该能够找到需要调整的内容.

The filter might need to be slightly adjusted, but when you see the output you should be able to find what needs to be adjusted.

MS文档的上述过滤器中使用的 DATEDIFF 函数中的更多信息可能是有用的参考.

Some more info in the DATEDIFF function used in the filter above from the MS docs might be a helpful reference.

docs.microsoft.com

在进行了上述更改之后，以下是有关从原始代码添加的详细信息的更新的查询和注释.

With the above changes, here is the updated query and comments as to the specifics added from your original code.

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;

这篇关于如何获取过去x周每种类型的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！