Create a new column in a sql query using case statement

问题: I have done a few table joins and below is how my data looks. Cust_No Account_No Product_H_f Product_H_L 123 A9023 Core Training 123 A9023 C...

问题:

I have done a few table joins and below is how my data looks.

Cust_No Account_No  Product_H_f Product_H_L
123     A9023         Core      Training
123     A9023         Core      Training
834     A9023         Core      Complimentary
634     A9024         Core      Complimentary
264     A9025         DTB       Training
263     A9025         DTB       Complimentary
677     A9025         DTB       Training

I want to add a column to this result that would be called 'Customer_no who have Training' and should look like this. (My original query is pretty complex with joins on 6 tables using left join)

Cust_No Account_No  Product_H_f Product_H_L Cust_has_Training
123    A9023        Core        Training        Yes
123    A9023        Core        Training        Yes
834    A9023        Core        Complimentary   Yes 
834    A9023        Core        Training        Yes
634    A9024        Core        Complimentary   No
264    A9025        DTB         Training        Yes
263    A9025        DTB         Complimentary   No
677    A9025        DTB         Training        Yes
677    A9025        DTB         Basic           Yes

I am using the below code to achieve this and it seems to be working. But since I have 48M records, it is taking forever to run. Is there a way I can optimize this query?

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
(CASE WHEN Cust_No IN (SELECT distinct(Cust_No) from table where 
Product_H_L='Training') then 'Yes' else 'No' end) as 'Cust_has_Training'
FROM TABLE 

回答1:

An EXISTS can perform really well for this purpose as can a sub-queried join. Without providing a whole lot more info, you probably won't get much better than a generic "this is usually faster".

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
(CASE WHEN Cust_has_Training.[Cust_No] is not null then 'Yes' else 'No' end) as 'Cust_has_Training'
FROM TABLE 
LEFT JOIN (
   SELECT distinct(Cust_No) AS [Cust_No]
   from table where 
   Product_H_L='Training'
) Cust_has_Training ON Cust_has_Training.[Cust_No]=Table.[Cust_No]

回答2:

How about using EXISTS()

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
(CASE WHEN EXISTS(SELECT 1 FROM TABLE WHERE Cust_No = T1.Cust_No AND
Product_H_L='Training') then 'Yes' else 'No' end) as 'Cust_has_Training'
FROM TABLE T1

回答3:

If you got a complex query the most efficient (and easiest) way to add this new calculation is probably based on Windowed Aggregates:

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
       -- returns YES when any row for a customer has 'Training'
       MAX(CASE WHEN Product_H_L='Training' THEN 'Yes' else 'No' end)
       OVER (PARTITION BY Cust_No) as 'Cust_has_Training'
FROM TABLE 
  • 发表于 2018-12-27 05:30
  • 阅读 ( 226 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除