SQL查询，如何在GROUP BY中选择一个生成的字段 [英] SQL query , How to select a generated field in GROUP BY

问题描述

我正在尝试选择customer_id和segment(基于某种逻辑)这两项，要求它们都在GROUP BY中

I am trying to select two things customer_id and segment(based on some logic) which require both of them to be in GROUP BY

我尝试不带别名使用(例如给CASE ... END)，但是由于GROUP BY字段名中存在MAX而失败.如果我切断了MAX，那么它也将无法编译.

I tried using without alias(like giving CASE...END) but it failed as there is MAX in a a GROUP BY fieldname . If I chop off MAX then also it fails to compile .

该怎么办才能解决这个问题?

What should I do to take care off this ?

推荐答案

我相信这在SQL Fiddle中对我有用:

I believe this was working for me in an SQL Fiddle:

alter session enable parallel query;

SELECT
  t1.CUSTOMER_ID AS CUSTOMER_ID,
  MAX(CASE WHEN t2.event_date=t1.event_date  -- MAX is moved all the way outside
           THEN 'SweepAcquired' 
           ELSE 'NonSweepAcquired'
           END) AS SEGMENT
FROM dsi t1, transaction t2
WHERE t1.CUSTOMER_ID=t2.CUSTOMER_ID(+)
AND t1.MARKETPLACE_ID = '1'
AND lower(t2.event_type(+))='like'
AND lower(t2.SOURCE_TYPE(+))='sweepstakes'
AND t2.SOURCE_ID(+) IS NOT NULL
AND lower(t2.SOURCE_ID(+)) <> 'unknown'
AND t2.ENTITY_ID(+)='1'
GROUP BY t1.CUSTOMER_ID, 
         CASE WHEN t2.event_date = t1.event_date
              THEN 1 
              ELSE 0 
              END
ORDER BY t1.CUSTOMER_ID, SEGMENT
;

由于某些原因，CASE WHEN MAX() CASE WHEN对我不起作用.我还基于WHERE子句中的重复项简化了几件事.

For some reason, CASE WHEN MAX() CASE WHEN wasn't working for me. I also simplified a couple things based on duplication in the WHERE clause.

在这里工作小提琴 http://sqlfiddle.com/#!4/630d7/46

这篇关于SQL查询，如何在GROUP BY中选择一个生成的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！