mysql - 如何对不同表和不同过滤器上的多个计数求和

Question

我想要box_id , date , hour和sum多个count id在不同的表中具有不同的 status在每个表中但具有相同的 box_id ,

示例

table 1
(filter by status = finished)


id   box_id              date                status
i     20      2019-01-01 01:00:00.000 UTC    finished
2     21      2019-01-01 02:00:00.000 UTC    finished
3     21      2019-01-01 01:00:00.000 UTC    unfinished


table 2
(filter by status = start)

id   box_id              date                status
i     21      2019-01-01 01:00:00.000 UTC    start
2     22      2019-01-01 02:00:00.000 UTC    end
3     23      2019-01-01 01:00:00.000 UTC    start
4     24      2019-01-01 01:00:00.000 UTC    start


table 3
(filter by status = close)


id   box_id              date                status
i     21      2019-01-01 03:00:00.000 UTC    close
2     22      2019-01-01 02:00:00.000 UTC    end
3     24      2019-01-01 01:00:00.000 UTC    close

result that i want:

box_id      date        hour       count
20         2019-01-01     1          1
21         2019-01-01     1          1
21         2019-01-01     2          1
21         2019-01-01     3          1
23         2019-01-01     1          1
24         2019-01-01     1          2

这是我适用于表 1 的查询:我如何在一张 table 上获得所有内容？

select box_id, 
date(date_update), 
EXTRACT(hour FROM date_update) as hourly, 
count(id)
from table1
where status = "finished"
group by box_id, date(date_update), EXTRACT(hour FROM date_update)

格式小时 = 0 - 23

Answer 1

假设您的日期字段为 TIMESTAMP 数据类型 - 以下适用于 BigQuery 标准 SQL

#standardSQL
SELECT box_id, date, hour, COUNT(1) cnt
FROM (
  SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour 
  FROM `project.dataset.table1` WHERE status = 'finished' UNION ALL
  SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour 
  FROM `project.dataset.table2` WHERE status = 'start' UNION ALL
  SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour 
  FROM `project.dataset.table3` WHERE status = 'close'
)
GROUP BY box_id, date, hour

您可以使用问题中的示例/虚拟数据来测试、玩上面的内容，如下例所示

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 1 id, 20 box_id, TIMESTAMP '2019-01-01 01:00:00.000 UTC'date, 'finished' status UNION ALL
  SELECT 2, 21, '2019-01-01 02:00:00.000 UTC', 'finished' UNION ALL
  SELECT 3, 21, '2019-01-01 01:00:00.000 UTC', 'unfinished' 
), `project.dataset.table2` AS (
  SELECT 1 id, 21 box_id, TIMESTAMP '2019-01-01 01:00:00.000 UTC' date, 'start' status UNION ALL
  SELECT 2, 22, '2019-01-01 02:00:00.000 UTC', 'end' UNION ALL
  SELECT 3, 23, '2019-01-01 01:00:00.000 UTC', 'start' UNION ALL
  SELECT 4, 24, '2019-01-01 01:00:00.000 UTC', 'start' 
), `project.dataset.table3` AS (
  SELECT 1 id, 21 box_id, TIMESTAMP '2019-01-01 03:00:00.000 UTC' date, 'close' status UNION ALL
  SELECT 2, 22, '2019-01-01 02:00:00.000 UTC', 'end' UNION ALL
  SELECT 3, 24, '2019-01-01 01:00:00.000 UTC', 'close' 
)
SELECT box_id, date, hour, COUNT(1) cnt
FROM (
  SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour 
  FROM `project.dataset.table1` WHERE status = 'finished' UNION ALL
  SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour 
  FROM `project.dataset.table2` WHERE status = 'start' UNION ALL
  SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour 
  FROM `project.dataset.table3` WHERE status = 'close'
)
GROUP BY box_id, date, hour
-- ORDER BY box_id, date, hour   

结果

Row box_id  date        hour    cnt  
1   20      2019-01-01  1       1    
2   21      2019-01-01  1       1    
3   21      2019-01-01  2       1    
4   21      2019-01-01  3       1    
5   23      2019-01-01  1       1    
6   24      2019-01-01  1       2      

下面是相同版本的稍微重构版本(显然具有相同的输出)

#standardSQL
SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour, 
  COUNTIF(
    (t = 1 AND status = 'finished') OR
    (t = 2 AND status = 'start') OR
    (t = 3 AND status = 'close') 
  ) cnt
FROM (
  SELECT 1 t, * FROM `project.dataset.table1` UNION ALL
  SELECT 2, * FROM `project.dataset.table2` UNION ALL
  SELECT 3, * FROM `project.dataset.table3` 
)
GROUP BY box_id, date, hour
HAVING cnt > 0

或者

#standardSQL
SELECT box_id, DATE(date) date, EXTRACT(HOUR FROM date) hour, COUNT(1) cnt
FROM (
  SELECT * FROM `project.dataset.table1` WHERE status = 'finished' UNION ALL
  SELECT * FROM `project.dataset.table2` WHERE status = 'start' UNION ALL
  SELECT * FROM `project.dataset.table3` WHERE status = 'close'
)
GROUP BY box_id, date, hour