过去X周获取数据时，子查询有太多列错误? [英] Subquery has too many columns error while getting data for past X weeks?
问题描述
我有以下查询，该查询为我提供了上周的数据，如下所示.它使用以下列中的 week_number 列返回上一列的数据: type ， amount 和 total 内部子查询.
I have below query which gives me data for previous week as shown below. It returns data with these columns: type, amount and total for previous week using week_number column which is used in inner subquery.
select type,
case
WHEN (type = 'PROC1' AND code = 'UIT') THEN 450
WHEN (type = 'PROC1' AND code = 'KJH') THEN 900
WHEN (type = 'PROC2' AND code = 'LOP') THEN 8840
WHEN (type = 'PROC2' AND code = 'AWE') THEN 1490
WHEN (type = 'PROC3' AND code = 'MNH') THEN 1600
WHEN (type = 'PROC3' AND code = '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 my above query which is for previous week:
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
在上面的查询中，我有一个内部子查询，如下所示，该子查询返回前一周的数据，然后在外部查询中使用其输出.
In my above query I have inner subquery as shown below which returns data for previous week and then output of this is used in the outer query.
select distinct entity_id from abc.items
where week_number = extract(week from current_date) - 1
and item_type like '%Ionize - Data%'
现在，我试图找出一种方式来获取过去6周(不包括当前周)的数据，并按每周进行分组，因此我认为我们需要修改上述内部查询，以便它可以给我过去6周的数据，然后每周以某种方式在外部查询中进行分组.基本上，我想在过去6周中为每种 type 获取 amount ， total ，如下所示.在某种程度上，我还需要在最终输出中添加 week_number 列.
Now I am trying to figure out a way by which I can get data for past 6 weeks (not including current week) and also grouped by each week so I think we need to modify above inner query so that it can give me data for past 6 weeks and then group by each week somehow at the outer query. Basically I want to get amount, total for each type for past 6 weeks as shown below. Somehow I need to add week_number column as well in my final output.
预期产量
week_number 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
所以我尝试通过修改内部子查询尝试以下查询，但由于 invalid operation:subquery有太多列，它给了我错误.知道我在这里做什么错了吗?
So I tried with below query by modifying inner subquery but it gives me error as invalid operation:subquery has too many columns. Any idea what is wrong I am doing here?
select type,
case
WHEN (type = 'PROC1' AND code = 'UIT') THEN 450
WHEN (type = 'PROC1' AND code = 'KJH') THEN 900
WHEN (type = 'PROC2' AND code = 'LOP') THEN 8840
WHEN (type = 'PROC2' AND code = 'AWE') THEN 1490
WHEN (type = 'PROC3' AND code = 'MNH') THEN 1600
WHEN (type = 'PROC3' AND code = '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 week_number, entity_id from abc.items
where week_number >= extract(week from current_date) - 6
and week_number <= extract(week from current_date) - 1
and item_type like '%Ionize - Data%'
order by week_number desc )
and createdfor ='BLOCK'
and holder='RELAY_FUTURES'
group by clientid) pb on
pa.clientid = pb.clientid and pa.version = pb.version;
我将内部查询修改为:
select week_number, entity_id from abc.items
where week_number >= extract(week from current_date) - 6
and week_number <= extract(week from current_date) - 1
and item_type like '%Ionize - Data%'
order by week_number desc
我可能完全错误地实现了期望的输出，因此将不胜感激.
It could be possible that I am doing it completely wrong to achieve the desired output so any help will be appreciated.
推荐答案
Andy问题出在WHERE子句
Andy the issue is with the WHERE clause
where consumerid IN (select week_number, entity_id from abc.items
where week_number >= extract(week from current_date) - 6
and week_number <= extract(week from current_date) - 1
and item_type like '%Ionize - Data%'
order by week_number desc )
该子句希望查看是否消耗"是由子查询产生的列表的成员.但是，此子查询返回2列，并且Redshift无法将1列与2列进行比较.只需删除"week_number"，从选择中，此错误将得到解决.
As the clause is looking to see if "consumerid" is a member of the list resulting from the subquery. However this subquery is returning 2 columns and Redshift cannot compare 1 column to 2 columns. Just remove "week_number," from the select and this error will be resolved.
where consumerid IN (select entity_id from abc.items
where week_number >= extract(week from current_date) - 6
and week_number <= extract(week from current_date) - 1
and item_type like '%Ionize - Data%'
order by week_number desc )
这篇关于过去X周获取数据时，子查询有太多列错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
