水平数据或垂直数据在 BigQuery 中更好

Question

我有两个表。每个表格都包含 1 月至 4 月的公司利润。为简单起见，我在表 1 中使用了月份名称。

1(哪张桌子比另一张更好？

2(我想找到每家公司从1月到4月的累计金额。

#standardSQL
with table1 as(
select "A" as comapany, 345 as Jan, 456 as Feb, 543 as March, 987 as April union all
select "B", 405, 956, 673, 907 union all
select "C", 745, 476, 323, 477
)
select * from table1
(or)
#standardSQL
with table2 as(
select  345 as A_profit, 456 as B_profit, 543 as C_profit,"1-31-2017" as year union all
select  405 , 956, 673,"2-28-2017" union all
select  745 , 476, 323,"3-31-2017" union all
select  515 , 736, 833,"4-30-2017"
)
select * from table2

Answer 1

哪张桌子比另一张更好？

没有一个是完美的，甚至不是好的：o(
以下是我建议的，以支持具有动态公司数量和任意时间框架等的大多数通用案例。
但我可以承认，如果你有非常具体的案例，只有少数几家公司和非常具体的短时间框架 - 你可能会发现表1或表2中的任何一个都很有用 - 但我怀疑它可以是一个实际的现实生活中的案例

#standardSQL
WITH table3 AS(
  SELECT "A" AS company, 345 AS profit, '1-31-2017' AS profit_date UNION ALL
  SELECT "A", 456, '2-28-2017' UNION ALL
  SELECT "A", 543, '3-31-2017' UNION ALL
  SELECT "A", 987, '4-30-2017' UNION ALL
  SELECT "B", 405, '1-31-2017' UNION ALL
  SELECT "B", 956, '2-28-2017' UNION ALL 
  SELECT "B", 673, '3-31-2017' UNION ALL 
  SELECT "B", 907, '4-30-2017' UNION ALL 
  SELECT "C", 745, '1-31-2017' UNION ALL 
  SELECT "C", 476, '2-28-2017' UNION ALL 
  SELECT "C", 323, '3-31-2017' UNION ALL 
  SELECT "C", 477, '4-30-2017' 
)

请注意 - 我不会像您的原始示例中那样在此处更改数据类型和使用 - 这是日期STRING

这将最终得到以下查询

。1月至4月各公司累计总和

#standardSQL
SELECT company, SUM(profit) profit
FROM table3
WHERE PARSE_DATE('%m-%d-%Y', profit_date) 
  BETWEEN DATE '2017-01-01' AND DATE '2017-04-30' 
GROUP BY company   

结果为

Row company profit   
1   A       2331     
2   B       2941     
3   C       2021     

为了避免迄今为止额外的解析字符串 - 您可以改用DATE类型，如下所示

#standardSQL
WITH table4 AS(
  SELECT "A" AS company, 345 AS profit, DATE '2017-01-31' AS profit_date UNION ALL
  SELECT "A", 456, DATE '2017-02-28' UNION ALL
  SELECT "A", 543, DATE '2017-03-31' UNION ALL
  SELECT "A", 987, DATE '2017-04-30' UNION ALL
  SELECT "B", 405, DATE '2017-01-31' UNION ALL
  SELECT "B", 956, DATE '2017-02-28' UNION ALL 
  SELECT "B", 673, DATE '2017-03-31' UNION ALL 
  SELECT "B", 907, DATE '2017-04-30' UNION ALL 
  SELECT "C", 745, DATE '2017-01-31' UNION ALL 
  SELECT "C", 476, DATE '2017-02-28' UNION ALL 
  SELECT "C", 323, DATE '2017-03-31' UNION ALL 
  SELECT "C", 477, DATE '2017-04-30' 
)
SELECT company, SUM(profit) profit
FROM table4
WHERE profit_date
  BETWEEN DATE '2017-01-01' AND DATE '2017-04-30' 
GROUP BY company     

显然具有相同的结果