下面是bigquery标准sql

# standardSQL

SELECT EventDate,
  b.total_sents - a.total_sents AS total_sents_diff,
  b.total_bounces - a.total_bounces AS total_bounces_diff,
  b.total_opens - a.total_opens AS total_opens_diff,
  b.total_clicks - a.total_clicks AS total_clicks_diff
FROM (
  SELECT EventDate,
    SUM(IF(EventType IN ('Bounce', 'Open'), EventCount, 0)) AS total_sents,
    SUM(IF(EventType = 'Bounce', EventCount, 0)) AS total_bounces,
    SUM(IF(EventType = 'Open', EventCount, 0)) AS total_opens,
    SUM(IF(EventType = 'Click', EventCount, 0)) AS total_clicks
  FROM `project.dataset.tableA`
  GROUP BY EventDate
) a
LEFT JOIN `project.dataset.tableB` b
USING(EventDate)

您可以使用下面的示例中的问题中的示例数据来测试、播放上述内容

# standardSQL

WITH `project.dataset.tableA` AS (
  SELECT '2020-07-05' EventDate, 'Bounce' EventType, 6 EventCount UNION ALL
  SELECT '2020-07-05', 'Click', 16737 UNION ALL
  SELECT '2020-07-05', 'Open', 187400 UNION ALL
  SELECT '2020-07-06', 'Bounce', 16 UNION ALL
  SELECT '2020-07-06', 'Click', 26737 UNION ALL
  SELECT '2020-07-06', 'Open', 387400 
), `project.dataset.tableB` AS (           
  SELECT '2020-07-05' EventDate, 155057 total_sents, 166 total_bounces, 75361 total_opens, 8783 total_clicks UNION ALL
  SELECT '2020-07-06', 255057, 266, 85361, 9783 
)
SELECT EventDate,
  b.total_sents - a.total_sents AS total_sents_diff,
  b.total_bounces - a.total_bounces AS total_bounces_diff,
  b.total_opens - a.total_opens AS total_opens_diff,
  b.total_clicks - a.total_clicks AS total_clicks_diff
FROM (
  SELECT EventDate,
    SUM(IF(EventType IN ('Bounce', 'Open'), EventCount, 0)) AS total_sents,
    SUM(IF(EventType = 'Bounce', EventCount, 0)) AS total_bounces,
    SUM(IF(EventType = 'Open', EventCount, 0)) AS total_opens,
    SUM(IF(EventType = 'Click', EventCount, 0)) AS total_clicks
  FROM `project.dataset.tableA`
  GROUP BY EventDate
) a
LEFT JOIN `project.dataset.tableB` b
USING(EventDate)

有输出

Row EventDate   total_sents_diff    total_bounces_diff  total_opens_diff    total_clicks_diff    
1   2020-07-05  -32349              160                 -112039             -7954    
2   2020-07-06  -132359             250                 -302039             -16954