用subqquery代替Case when以避免错误(case语句中When子句中的非法表达式) [英] Alternative to Case when with subqquery to avoid error (Illegal expression in When clause within case statement)
问题描述
我的表结构与此类似
Customer_id Country item_type Order_Size Dates Codes
A401 US Fruit Small 3/14/2016 11
A401 US Fruit Big 5/22/2016 12
A401 US Vegetable Small 7/12/2016 11
B509 US Vegetable Small 3/25/2015 92
B509 US Vegetable Big 3/15/2014 11
B509 US Vegetable Small 3/1/2014 34
A402 CA Fruit Small 3/14/2016 56
A402 CA Fruit Big 5/22/2016 76
A402 CA Fruit Small 7/12/2016 85
A403 CA Vegetable Small 7/12/2016 11
A403 CA Vegetable Small 3/25/2015 16
A403 CA Vegetable Big 3/15/2014 17
A403 CA Vegetable Small 3/1/2014 12
我正在寻找每个国家/地区，仅在购买了Order_size = Big且仅购买了order_size<> Big的商品之后，每种item_type的重复顾客数量是多少.为此，我编写了这段代码.
I am looking for each country how many repeated customers for each item_type are present only after they purchased Order_size =Big and only items purchased with order_size<>Big. To achieve this I wrote this code.
SELECT Country,item_type,count(customer_id) from
(select Country,customer_id, t.item_type, count(*) as REPEATS
from (select t.*,
min(case when Order_Size = 'Big' then dates end) over (partition by customer_id, item_type) as min_big
from data_test as t
) t
where dates > min_big
group by 1,2,3) D
group by 1,2
结果:
Country item_type Count(Distinct(Customer_id))
CA Vegetable 1
US Vegetable 1
CA Fruit 1
这现在可以工作，但是我只想在代码位于带有条件的某些表中时再添加一个条件，所以我想添加多个条件，其中一个是在修改代码的情况下进行子查询.
This works now but I wanted to add one more condition as to only when the codes are within certain table with condition so I wanted to add multiple conditions with one being subquery with the case when I modified my code.
SELECT Country,item_type,count(customer_id) from
(select Country,customer_id, t.item_type, count(*) as REPEATS
from (select t.*,
min(case when (Order_Size = 'Big' and Codes IN (SELECT CODES from table1 where type='TRUE' group by 1)) then dates end) over (partition by customer_id, item_type) as min_big
from data_test as t
) t
where dates > min_big
group by 1,2,3) D
group by 1,2
这将引发错误-case语句中的when子句中的表达式非法.我还读到您不能在大小写内使用子查询，也不能使用 IN .我已经阅读了许多与此相关的其他问题，但是对于如何避免使用子查询，我仍然不清楚. 我该如何更改我的代码，该代码不会抛出错误，并且由于我的表很大，因此可以快速处理?
This is throwing an error - Illegal expression in When clause within case statement. I also read that you cant use subquery within case and also use of IN. I have read many other questions related to this but I am still not clear as to how I can avoid using subquery in case. How do I change my code which does not throw error and also could be processed fast as my table is very large?
推荐答案
在其他规则之前，您可以更改代码以连接到驱动答案是否应包含此代码"的表.确保对代码字段上不同的数据集进行外部联接，以防止重复.
You can change your code to connect to the table that drives the answer to "is this code supposed to be included" before the other rules. Be sure to do an outer join to a dataset that is distinct on the code field to prevent dupes.
此方法将条件条件的结果包括在所有其他规则之前，并且不会引发错误.下面，我在代码上创建了一个具有唯一主索引的volatile表，但您可以加入一个派生表并获得相似的结果.
This approach includes the result of your conditional before all the other rules and won't raise an error. Below I created a volatile table with a unique primary index on the code to do this but you could join to a derived table and get a similar result.
create volatile table vt_fruit_exp
( Customer_id char(4)
, Country char(2)
, item_type varchar(20)
, Order_Size char(5)
, Dates date
, Codes byteint)
primary index (Customer_id) on commit preserve rows;
insert into vt_fruit_exp values('A401','US','Fruit' ,'Small' ,'2016-03-14', 11);
insert into vt_fruit_exp values('A401','US','Fruit' ,'Big' ,'2016-05-22', 12);
insert into vt_fruit_exp values('A401','US','Vegetable','Small' ,'2016-07-12', 11);
insert into vt_fruit_exp values('B509','US','Vegetable','Small' ,'2015-03-25', 92);
insert into vt_fruit_exp values('B509','US','Vegetable','Big' ,'2014-03-15', 11);
insert into vt_fruit_exp values('B509','US','Vegetable','Small' ,'2014-03-01', 34);
insert into vt_fruit_exp values('A402','CA','Fruit' ,'Small' ,'2016-03-14', 56);
insert into vt_fruit_exp values('A402','CA','Fruit' ,'Big' ,'2016-05-22', 76);
insert into vt_fruit_exp values('A402','CA','Fruit' ,'Small' ,'2016-07-12', 85);
insert into vt_fruit_exp values('A403','CA','Vegetable','Small' ,'2016-07-12', 11);
insert into vt_fruit_exp values('A403','CA','Vegetable','Small' ,'2015-03-25', 16);
insert into vt_fruit_exp values('A403','CA','Vegetable','Big' ,'2014-03-15', 17);
insert into vt_fruit_exp values('A403','CA','Vegetable','Small' ,'2014-03-01', 12);
create volatile table Table1
( Codes byteint,Code_In_flg byteint) unique primary index (Codes)
on commit preserve rows
;
insert into Table1 values (11,1);
insert into Table1 values (76,1);
insert into Table1 values (12,1);
-- Each country-> how many repeated customers for each item_type are present AFTER they purchased Order_size=Big. Only items purchased with order_size<>Big
-- Country item_type Count(Distinct(Customer_id))
-- CA Vegetable 1
-- US Vegetable 1
-- CA Fruit 1
SELECT
Country
, item_type
, count(customer_id)
FROM (
select Country,customer_id, t.item_type, count(*) as REPEATS
from (
Select
t.*
, Min(Case When Order_Size = 'big' Then Dates End) Over (Partition By Customer_Id, Item_Type) As Min_Big
From vt_fruit_exp As T
) t
where dates > min_big
group by 1,2,3) D
group by 1,2;
-- This works now but I wanted to add one more condition as to only when the codes are within certain table with condition so I wanted to add multiple conditions with one being subquery with the case when I modified my code.
-- use a join to the table that refers to whether the code is to be included or not instead of attempting a subquery withing ordered analytic
SELECT
Country
, item_type
, count(customer_id)
FROM (
select Country,customer_id, t.item_type, count(*) as REPEATS
from (
Select
t.*
, Min(Case When Order_Size = 'big' And b.Code_In_flg=1 Then Dates End) Over (Partition By Customer_Id, Item_Type) As Min_Big
from vt_fruit_exp T left outer join Table1 B on t.Codes=b.Codes
) t
where dates > min_big
group by 1,2,3) D
group by 1,2
这篇关于用subqquery代替Case when以避免错误(case语句中When子句中的非法表达式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
