bigquery中基于id和时间的sql分组数据

6yoyoihd 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(329)

我正在做一个查询，可以根据id和时间聚合数据。我已经成功地基于id聚合了数据，但是当我添加时间时，查询无法识别时间列。
示例代码：

SELECT
DATE(Request_Timestamp) as Date,
     ID, 
 SUM(CASE WHEN Language = 'EN' THEN 1 ELSE 0 END) AS lang_EN,
 SUM(CASE WHEN Language = 'SN' THEN 1 ELSE 0 END) AS lang_SN,

 FROM (
   SELECT 
    DATE(Request_Timestamp),
     ID, 
      CASE 
        WHEN Language in ('EN', 'English') THEN 'EN' 
        WHEN Language in ('MY', 'Malay')  THEN 'MY' ELSE Language 
      END AS Language,

   FROM table
   GROUP BY   ID, Language, DATE(Request_Timestamp)
 )
 GROUP BY  ID, DATE(Request_Timestamp)
)

样本输入：

样本输出

错误（选择后）：

Unrecognized name: Request_Timestamp at [9:6]

更新：它与request\u timestamp一起工作，但不与date（request\u timestamp）一起工作

sql google-bigquery

来源：https://stackoverflow.com/questions/62789615/grouping-data-based-on-id-and-time-in-bigquery

3条答案

按热度按时间

wixjitnu1#

解决了的：

SELECT
     DATE,
        ..

     FROM (
       SELECT 
        DATE(Request_Timestamp)as DATE,
         ID, 
          CASE 
           ..
          END AS Language,

       FROM table
       GROUP BY   ID, Language, Request_Timestamp
     ) t
     GROUP BY ID, DATE, Language

赞(0）回复(0）举报 2021-07-24

4nkexdtk2#

别名被遗漏了，我被删除了吗

SELECT
DATE(Timestamp),
     ID, 
 SUM(CASE WHEN Language = 'EN' THEN 1 ELSE 0 END) AS lang_EN,
 SUM(CASE WHEN Language = 'SN' THEN 1 ELSE 0 END) AS lange_SN

 FROM                
   FROM table
 GROUP BY  ID, DATE(Timestamp)

赞(0）回复(0）举报 2021-07-24

5lwkijsr3#

您似乎只需要条件聚合。因为您只想计算匹配的布尔条件，所以使用 COUNTIF() :

SELECT DATE(Request_Timestamp) as Date, ID, 
       COUNTIF(Language in ('EN', 'English')) AS lang_EN,
       COUNTIF(Language in ('MY', 'Malay')) AS lang_MY
FROM table
GROUP BY DATE, ID;

赞(0）回复(0）举报 2021-07-24

我来回答

bigquery中基于id和时间的sql分组数据

3条答案

相关问题

热门标签

最新问答