无需PIVOT指令实现BigQuery表动态转置（CMD单步执行）

需求说明 #

现有Google BigQuery子查询输出如下三列CSV数据，要求在DOS窗口（CMD.EXE）中通过单步bq命令执行，不支持多命令脚本序列：

service, usagedate, cost
Cats, 2023-07-05, 440.51
Cats, 2023-07-06, 368.36
Cats, 2023-07-07, 0.00
Dogs, 2023-07-05, 10.90
Dogs, 2023-07-06, 10.78
Dogs, 2023-07-07, 3.88

曾尝试以下方法，但因查询今日、昨日列时返回多行重复日期而失效；原生PIVOT指令要求列头为常量，无法满足用「今日」附近动态日期作为列头的需求：

WITH (*foo*) AS subquery SELECT DISTINCT service, (SELECT cost FROM subquery WHERE usagedate = CURRENT_DATE()) AS A, (SELECT cost FROM subquery WHERE usagedate = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) as B FROM subquery

目标是生成如下格式的表：提取唯一service值作为首列，将连续动态日期转为同行列，后续再将首行的“A”替换为实际日期：

service, A, B
Cats, 454.43, 455.04
Dogs, 10.86, 10.90

可行实现代码 #

以下是经验证可行的单步bq命令代码，后续可按需优化：

call bq query --quiet --format=csv --max_rows=1234567890 --use_legacy_sql=false "WITH  snip AS ( SELECT DISTINCT service.description AS SERVICE,    service.id AS ID, cast(usage_end_time as date) as UsageDate,  SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c where c.name not like 'migration-credit%%'),0)) AS COST FROM %targetdataset%  WHERE service.id in(select distinct service.id from %targetdataset%  WHERE  project.id = '%projectid%'    and    cast(usage_end_time as date) > date_sub(CURRENT_DATE(), INTERVAL 10 day))   GROUP BY 1, 2, 3 ORDER BY SERVICE, UsageDate DESC ), snip2 as (SELECT DISTINCT SERVICE, ID, UsageDate, sum(COST) as COST from snip where UsageDate > date_sub(CURRENT_DATE(), INTERVAL 10 day)  and COST > 0 GROUP BY 1, 2, 3), snip3 as (select SERVICE,   sum(if(UsageDate = current_date - 9, COST, 0)) A, sum(if(UsageDate = current_date - 8, COST, 0)) B,  sum(if(UsageDate = current_date - 7, COST, 0)) C,  sum(if(UsageDate = current_date - 6, COST, 0)) D, sum(if(UsageDate = current_date - 5, COST, 0)) E,  sum(if(UsageDate = current_date - 4, COST, 0)) F,  sum(if(UsageDate = current_date - 3, COST, 0)) G,  sum(if(UsageDate = current_date - 2, COST, 0)) H,  sum(if(UsageDate = current_date - 1, COST, 0)) I,  sum(if(UsageDate = current_date, COST, 0)) J,  from snip2 group by SERVICE) select SERVICE, trim(cast(A as STRING FORMAT '99999990.99')), trim(cast(B as STRING FORMAT '99999990.99')), trim(cast(C as STRING FORMAT '99999990.99')), trim(cast(D as STRING FORMAT '99999990.99')), trim(cast(E as STRING FORMAT '99999990.99')), trim(cast(F as STRING FORMAT '99999990.99')), trim(cast(G as STRING FORMAT '99999990.99')), trim(cast(H as STRING FORMAT '99999990.99')), trim(cast(I as STRING FORMAT '99999990.99')), trim(cast(J as STRING FORMAT '99999990.99')), from snip3 order by 1 "

代码逻辑说明 #

1. 多层CTE逐步清洗数据：snip筛选目标项目下近10天的记录，计算含抵扣后的实际成本；snip2过滤成本为0的无效数据并二次聚合
2. 在snip3中用SUM(IF(...))实现动态日期行转列，将近10天的成本分别映射到A-J列，避开原生PIVOT对常量列头的限制
3. 最后格式化成本数值为指定格式，确保输出CSV的可读性

内容的提问来源于stack exchange，提问作者Dave