mysql - 大查询 : Consolidate subselect into one row by date

Question

我正在尝试让包含子查询的 BigQuery 查询结果返回一行而不是两行。我正在查询日志文件，所以我需要的所有数据都在同一个字段中。该字段中的数据示例如下:

/?cv=p15.0.9350&ctyp=sp&bits=64&os_bits=64&hl=fr&hl=fr&os=win&osv=6.2   

我一直在处理的查询如下:

SELECT day, Win, Mac 
  FROM
    (SELECT DATE(metadata.timestamp) AS day, COUNT(DISTINCT protoPayload.resource) AS Win
     FROM [su_dashboard_streamed_logs.appengine_googleapis_com_request_log_20150424]
     WHERE protoPayload.resource CONTAINS 'ctyp=sp'
     GROUP BY day),
    (SELECT DATE(metadata.timestamp) AS day, COUNT(DISTINCT protoPayload.resource) AS Mac
     FROM [request_log_20150424]
     WHERE protoPayload.resource CONTAINS 'ctyp=sm'
     GROUP BY day)
ORDER BY day

目前上面的查询返回:

Row day Win Mac  
1   2015-04-24  160516  null     
2   2015-04-24  null    109547  

我希望结果是:

Row day Win Mac
1 2015-04-24 160516 109547

有没有办法做到这一点？如果是这样，我们将不胜感激。

谢谢

Answer 1

您想 JOIN 两个子选择而不是合并它们。在 BigQuery 中，a comma within a FROM clause indicates a union :

Note: Unlike many other SQL-based systems, BigQuery uses the comma syntax to indicate table unions, not joins.

如果您在日期字段上JOIN，那么您可以使用类似下面的内容将两行压缩成一行:

SELECT table_1.day as day, table_1.Win as Win, table_2.Mac AS Mac
  FROM
    (SELECT DATE(metadata.timestamp) AS day, COUNT(DISTINCT protoPayload.resource) AS Win
     FROM [su_dashboard_streamed_logs.appengine_googleapis_com_request_log_20150424]
     WHERE protoPayload.resource CONTAINS 'ctyp=sp'
     GROUP BY day) AS table_1
  JOIN
    (SELECT DATE(metadata.timestamp) AS day, COUNT(DISTINCT protoPayload.resource) AS Mac
     FROM [request_log_20150424]
     WHERE protoPayload.resource CONTAINS 'ctyp=sm'
     GROUP BY day) AS table_2
  ON table_1.day = table_2.day
ORDER BY day