redhisft sql子查询使用外部查询中的未分组列

ibps3vxo 于 2021-08-01 发布在 Java

关注(0)|答案(1)|浏览(333)

我在尝试执行子查询时遇到以下错误子查询使用外部查询中未分组的列“s.event\u captured\u dt”

select

to_date(s.event_captured_dt,'DD Mon YYYY')
,count(*) as count_
,(select count(*) 
    from spc_raw_responsys_kanui.sent s1
    where to_date(s1.event_captured_dt,'DD Mon YYYY') between to_date(s.event_captured_dt,'DD Mon YYYY') - 7 and to_date(s.event_captured_dt,'DD Mon YYYY')
) as count7days_

from spc_raw_responsys_kanui.sent s

where 1=1 
and to_date(s.event_captured_dt,'DD Mon YYYY') >= '2020-06-01 00:00:00'

group by to_date(s.event_captured_dt,'DD Mon YYYY')

这就是我想要达到的目标：
目标

sql Date subquery select amazon-redshift

来源：https://stackoverflow.com/questions/62647299/redhisft-sql-subquery-uses-ungrouped-column-from-outer-query

1条答案

按热度按时间

0tdrvxhp1#

如果在子查询中进行预聚合，是否有效？

select
    s.*,
    (
        select count(*) 
        from spc_raw_responsys_kanui.sent s1
        where to_date(s1.event_captured_dt,'DD Mon YYYY') 
            between s.event_captured_date - 7 
            and s.event_captured_date
    ) as count7days_
from (
    select to_date(event_captured_dt,'DD Mon YYYY') event_captured_date, count(*) as count_
    from spc_raw_responsys_kanui.sent s
    where to_date(event_captured_dt,'DD Mon YYYY') >= '2020-06-01 00:00:00'
    group by to_date(event_captured_dt,'DD Mon YYYY')
) s

赞(0）回复(0）举报 2021-08-01

我来回答

redhisft sql子查询使用外部查询中的未分组列

1条答案

相关问题

热门标签

最新问答