首页 > 解决方案 > 检索每个 ID 具有唯一时间戳的数据,从中检索数据

问题描述

目前使用 Looker SQL Runner,我需要检索每个网站 ID 发生的会话总数,每个网站 ID 都有不同的时间点,我需要从开始到该时间点检索数据。

格式将是这样的:

website_id 日期 总会话数 总用户数
1 2021-08-13 100 98
2 2021-07-15 125 105
3 2021-06-29 98 85

现在,我的代码如下所示:

with base as
(
SELECT
      website_id
      ,session_count
      ,user_id
      ,'timestamp'
FROM page_views_y2
WHERE website_id in (100604,100577,1078,100501)
GROUP BY 1,2,3
),

min_time as
(
SELECT
      website_id
      ,session_count
      ,user_id
      ,min(timestamp) as session_start
FROM page_views_y2
WHERE website_id in (100604,100577,1078,100501)
GROUP BY 1,2,3
),

combined_count AS
(
SELECT
      base.website_id
      ,session_start
      ,COUNT(DISTINCT min_time.user_id||session_start||min_time.session_count) AS distinct_combined_count
      ,COUNT(DISTINCT base.user_id) AS distinct_users
FROM base
LEFT JOIN min_time
ON base.user_id=min_time.user_id
AND base.website_id=min_time.website_id
GROUP BY 1,2
)

SELECT
      website_id
      ,CASE 
        WHEN website_id=100604 AND session_start < (timestamp '2021-07-27') THEN distinct_combined_count
        WHEN website_id in (100577,1078) AND session_start < (timestamp '2021-07-26') THEN distinct_combined_count
        WHEN website_id=100501 AND session_start < (timestamp '2021-07-16') THEN distinct_combined_count
      ELSE 0 END AS final_count
      ,CASE 
        WHEN website_id=100604 AND session_start < (timestamp '2021-07-27') THEN distinct_users
        WHEN website_id in (100577,1078) AND session_start < (timestamp '2021-07-26') THEN distinct_users
        WHEN website_id=100501 AND session_start < (timestamp '2021-07-16') THEN distinct_users
      ELSE 0 END AS final_distinct_users
FROM combined_count
GROUP BY 1

但是我一直遇到错误:列“combined_count.session_start”必须出现在 GROUP BY 子句中,或者在聚合函数中使用,尽管它显然在 group by 中。

此外,这只是四个网站 ID,其中我还需要处理另外 30 个,所以如果我能让这四个工作,那么我可以将其扩展到整个 ID 列表及其各自的时间点。

感谢您的时间。

标签: sqlsyntax-errorcommon-table-expressionlooker

解决方案


您需要汇总“CASE”语句。


推荐阅读