如何给出其中＆＃x27;选择列时选择查询的条件？

Question

我想在执行Select语句时在列选择中提供条件。

我想进行总计_timeonsite的平均值，重命名，并希望平均为Jun'20，Jul'20和Aug'20对访客的月份的平均值。

而且，整个查询的范围必须仅是8月20日的月份。因此，我想对total_timeonsite限制，以便将Jun'20，Jul'20和8月20日的几个月的值与访客相提并论。

select FULLVISITORID AS VISITOR_ID,
VISITID AS VISIT_ID,
VISITSTARTTIME_TS,
USER_ACCOUNT_TYPE,
(select AVG(TOTAL_TIMEONSITE) AS AVG_TOTAL_TIME_ON_SITE_LAST_3M FROM "ACRO_DEV"."GA"."GA_MAIN" WHERE
 (cast((visitstarttime_ts) as DATE) >= to_date('2020-06-01 00:00:00.000') and CAST((visitstarttime_ts) AS DATE) <= to_date('2020-08-31 23:59:00.000'))
 GROUP BY TOTAL_TIMEONSITE),
CHANNELGROUPING,
GEONETWORK_CONTINENT
from "ACRO_DEV"."GA"."GA_MAIN"
where (FULLVISITORID) in (select distinct (FULLVISITORID) from "ACRO_DEV"."GA"."GA_MAIN" where user_account_type in ('anonymous', 'registered') 
and (cast((visitstarttime_ts) as DATE) >= to_date('2020-08-01 00:00:00.000') and CAST((visitstarttime_ts) AS DATE) <= to_date('2020-08-31 23:59:00.000')));

问题在于，它为我提供了“ total_timeonsite的选择子查询”为结果列名称，该列中的值都是相同的，但我希望这些值对访问者来说是唯一的。

Answer 1

因此，对于雪花：

所以我要假设访问starttime_ts是timestamp因此
cast（（（（访问starttime_ts）作为日期）与`extstarttime_ts :: date'giss

select to_timestamp('2020-08-31 23:59:00') as ts
    ,cast((ts) as DATE) as date_a
    ,ts::date as date_b;

：

ts	date_a date_b	2020-08-31
23：59：00.000	2020-08-31	202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020

-ADS :: DATE'给予：因此，日期范围也可以更简单

select to_timestamp('2020-08-31 13:59:00') as ts
    ,cast((ts) as DATE) as date_a
    ,ts::date as date_b
    ,date_a >= to_date('2020-08-01 00:00:00.000') and date_a <= to_date('2020-08-31 23:59:00.000') as comp_a
    ,date_b >= to_date('2020-08-01 00:00:00.000') and date_b <= to_date('2020-08-31 23:59:00.000') as comp_b
    ,date_b >= '2020-08-01'::date and date_a <= '2020-08-31 23:59:00.000'::date as comp_c
    ,date_b between '2020-08-01'::date and '2020-08-31 23:59:00.000'::date as comp_d

ts_a	date_a	date_b	comp_a	comp_b	comp_c	comp_d
2020-08-31 13：59：00.000	2020-08-31	2020-08-31	true true true True	True True	True True	Engry

无论如何，如果我知道您想要什么像使用CTE一样将其写入（对我来说）更可读性：

with distinct_aug_ids as (
    SELECT DISTINCT 
        fullvisitorid 
    FROM acro_dev.ga.ga_main
    WHERE user_account_type IN ('anonymous', 'registered') 
        AND visitstarttime_ts::date BETWEEN '2020-08-01::date AND '2020-08-31'::date
), three_month_avg as (
    SELECT 
        fullvisitorid
        ,AVG(total_timeonsite) AS avg_total_time_on_site_last_3m
    FROM acro_dev.ga.ga_main
    WHERE visitstarttime_ts::DATE BETWEEN to_date('2020-06-01 00:00:00.000') AND to_date('2020-08-31 23:59:00.000')
    GROUP BY 1
)
select 
    m.fullvisitorid as visitor_id,
    m.visitid as visit_id,
    m.visitstarttime_ts,
    m.user_account_type,
    tma.avg_total_time_on_site_last_3m,
    m.channelgrouping,
    m.geonetwork_continent
FROM acro_dev.ga.ga_main as m
JOIN distinct_aug_ids AS dai
    ON m.fullvisitorid = dai.fullvisitorid
JOIN three_month_avg AS tma
    ON m.fullvisitorid = tma.fullvisitorid
;

但是，如果您希望它是子选择，它们是相同的：

select 
    m.fullvisitorid as visitor_id,
    m.visitid as visit_id,
    m.visitstarttime_ts,
    m.user_account_type,
    tma.avg_total_time_on_site_last_3m,
    m.channelgrouping,
    m.geonetwork_continent
FROM acro_dev.ga.ga_main as m
JOIN (
    SELECT DISTINCT 
        fullvisitorid 
    FROM acro_dev.ga.ga_main
    WHERE user_account_type IN ('anonymous', 'registered') 
        AND visitstarttime_ts::date BETWEEN '2020-08-01::date AND '2020-08-31'::date
) AS dai
    ON m.fullvisitorid = dai.fullvisitorid
JOIN (
    SELECT 
        fullvisitorid
        ,AVG(total_timeonsite) AS avg_total_time_on_site_last_3m
    FROM acro_dev.ga.ga_main
    WHERE visitstarttime_ts::DATE BETWEEN to_date('2020-06-01 00:00:00.000') AND to_date('2020-08-31 23:59:00.000')
    GROUP BY 1
)AS tma
    ON m.fullvisitorid = tma.fullvisitorid
;