sql - 大数据条件聚合

Question

我有一个快速的问题，我试图敲打我的头，但没有成功：

假设我有下表：

+-----+----------------+-----+-------+------+------+------+-------+
| Row | Promotion Name | Day | Month | Year | SENT | Open | Click |
+-----+----------------+-----+-------+------+------+------+-------+
|   1 | Email_New_V1   |   1 |     2 | 2019 |    3 |    2 |     1 |
|   2 | Email_New_V2   |   1 |     2 | 2019 |    5 |    2 |     1 |
|   3 | Email_New_V3   |   2 |     2 | 2019 |    4 |    2 |     1 |
+-----+----------------+-----+-------+------+------+------+-------+

基本上，我想要每天（第 1 天、第 2 天等）和每月（第 1 个月、第 2 个月等）的 SENT、OPEN 和 CLICK 总数......由促销中的前几个字符聚合姓名（Email_New%）

基本上，我会有这个：

 +----------------+-----+-------+------+------+------+-------+
    | Promotion Name | Day | Month | Year | SENT | Open | Click |
    +----------------+-----+-------+------+------+------+-------+
    | Email_New      |   1 |     2 | 2019 |   12 |    6 |     3 |
    +----------------+-----+-------+------+------+------+-------+

我尝试使用 SUBSTR 选择前几个单词，但无济于事。我可以给小费吗？

非常感谢！

标签： sqlstringgroup-bygoogle-bigquery

Answer 1

下面是 BigQuery 标准 SQL 的示例

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Email_New_V1' promotion_name, 1 day, 2 month, 2019 year, 3 sent, 2 open, 1 click UNION ALL
  SELECT 'Email_New_V2', 1, 2, 2019, 5, 2, 1 UNION ALL
  SELECT 'Email_New_V3', 2, 2, 2019, 4, 2, 1 UNION ALL
  SELECT 'Email_Old_V1', 1, 2, 2019, 1, 2, 3 UNION ALL
  SELECT 'Email_Old_V2', 1, 2, 2019, 4, 5, 6 
), promotions AS (
  SELECT 'Email_New' promotion_name UNION ALL
  SELECT 'Email_Old'
)
SELECT p.promotion_name, 
  day, month, year, 
  SUM(sent) sent,
  SUM(open) open,
  SUM(click) click 
FROM `project.dataset.table` t
JOIN promotions p
ON STARTS_WITH(t.promotion_name, p.promotion_name)
GROUP BY promotion_name, day, month, year      

带输出

Row promotion_name  day month   year    sent    open    click    
1   Email_New       1   2       2019    8       4       2    
2   Email_New       2   2       2019    4       2       1    
3   Email_Old       1   2       2019    5       7       9