BigQuery 中的动态标头 [英] Dynamic Header in BigQuery

问题描述

我正在创建一个仪表板，我想在其中显示每个类别每周关闭的项目.这是我的表的样本.

I'm creating a dashboard where I want to show the items closed per week per category. This is a sample of my table.

<头>

类别	开业日期	关闭日期
销售	06/01/2021	06/02/2021
产​​品	06/02/2021	06/07/2021
反馈	06/07/2021	06/14/2021
销售	05/18/2021	05/23/2021
产​​品	06/01/2021	06/01/2021
反馈	06/01/2021	06/05/2021
销售	05/21/2021	05/24/2021
产​​品	05/21/2021	05/26/2021
产​​品	06/01/2021	06/02/2021
反馈	05/31/2021	06/13/2021
销售	06/02/2021	06/06/2021
产​​品	06/04/2021	06/07/2021

这就是我想要达到的结果.

This is the result I want to achieve.

每周关闭类别

<头>

类别	05/23/2021	05/30/2021	06/06/2021	06/13/2021
销售	2	1	0	1
产​​品	1	2	2	0
反馈	0	0	1	2

我尝试使用 SET 以便我可以动态创建列，但是当我使用 @ 符号时 bigQuery 显示错误.当我使用 column_alias 时，它也按字面显示列名

I tried to use the SET so I can dynamically create the column but bigQuery shows an error when I use the @ sign. Also it shows literally the column name when I use the column_alias

SET @column_week1 = DATE_SUBB(CURRENT_DATE, INTERVAL 7 DAYS)

SET @column_ week1 = DATE_SUBB(CURRENT_DATE, INTERVAL 7 DAYS)

还有其他选择可以实现这一目标吗?

Any other options to achieve this?

推荐答案

一枪搞定

execute immediate (             
select '''select * from (
  select replace('_' || (last_day(parse_date('%m/%d/%Y', closed), week) - 6), '-', '_') week, 
    category
  from `project.dataset.table`
  )
  pivot(count(*) for week in ("''' ||  string_agg(week, '", "')  || '''"))
'''
from (select distinct replace('_' || (last_day(parse_date('%m/%d/%Y', closed), week) - 6), '-', '_') week from `project.dataset.table` order by week) 
)      

带输出

也请查看https://stackoverflow.com/a/67479622/5221944 - 可以帮助您理解上述内容代码

Check out also https://stackoverflow.com/a/67479622/5221944 - can help you in understanding above code

这篇关于BigQuery 中的动态标头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！