英文:
BigQuery sum counts from start of year to date
问题
我有一个表格（TEST_TABLE），它给出了每个月最后一天（LAST_DAY_MONTH）每个公司（COMPANNY）的人数（HEADCOUNT）。
一个公司的示例：
| LAST_DAY_MONTH | COMPANNY | HEADCOUNT |
|---|---|---|
| 2023-01-31 | x1 | 20 |
| 2023-02-28 | x1 | 22 |
| 2023-03-31 | x1 | 21 |
| 2023-04-30 | x1 | 27 |
是否有可能创建一个新表格，其中包含累计的截至日期人数？
例如：
| LAST_DAY_MONTH | COMPANNY | HEADCOUNT_TO_DATE |
|---|---|---|
| 2023-01-31 | x1 | 20 |
| 2023-02-28 | x1 | 42 |
| 2023-03-31 | x1 | 63 |
| 2023-04-30 | x1 | 90 |
我已经尝试过以下查询语句：
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(CASE WHEN LAST_DAY_MONTH BETWEEN DATE_TRUNC(LAST_DAY_MONTH, year) AND LAST_DAY_MONTH THEN HEADCOUNT END)
FROM `TEST_TABLE`
GROUP BY 1,2
但这只给出了每个月的人数。
编辑：
我只想对同一年的人数求和，所以如果我查看2022-12-31的行，HEADCOUNT_TO_DATE应该是2022年所有12个月的人数总和。
在2023-01-31，它应该只求和2023年的第一个月的人数。
英文:
I Have a table (TEST_TABLE) which gives me the HEADCOUNT on the last day of each month (LAST_DAY_MONTH) for all individual companies (COMPANNY)
an example for 1 company:
| LAST_DAY_MONTH | COMPANNY | HEADCOUNT |
|---|---|---|
| 2023-01-31 | x1 | 20 |
| 2023-02-28 | x1 | 22 |
| 2023-03-31 | x1 | 21 |
| 2023-04-30 | x1 | 27 |
Is there a possibility to create a new table with a summed headcount to date?
i.e
| LAST_DAY_MONTH | COMPANNY | HEADCOUNT_TO_DATE |
|---|---|---|
| 2023-01-31 | x1 | 20 |
| 2023-02-28 | x1 | 42 |
| 2023-03-31 | x1 | 63 |
| 2023-04-30 | x1 | 90 |
I allready tried
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(CASE WHEN LAST_DAY_MONTH BETWEEN DATE_TRUNC(LAST_DAY_MONTH, year) AND LAST_DAY_MONTH THEN HEADCOUNT END)
FROM `TEST_TABLE`
GROUP BY 1,2
But this just gives the headcount of each month
EDIT:
I ony want to sum headcounts from the same year so if I would look at the row of 2022-12-31, the HEADCOUNT_TO_DATE should be the sum of all 12 months of 2022.
On 2023-01-31 it shoud then only sum the headcount of the first month of 2023
答案1
得分: 1
你可以使用窗口函数来计算每个公司的headcount的累计总和，直到当前行。
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME ORDER BY LAST_DAY_MONTH) AS HEADCOUNT_TO_DATE
FROM
TEST_TABLE;
如果只针对同一年进行计算，可以使用以下查询语句：
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME, EXTRACT(YEAR FROM LAST_DAY_MONTH) ORDER BY LAST_DAY_MONTH) AS HEADCOUNT_TO_DATE
FROM
TEST_TABLE;
英文:
You can use a window function to calculate the running sum of the headcount for each company up to the current row.
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME ORDER BY LAST_DAY_MONTH) AS HEADCOUNT_TO_DATE
FROM
TEST_TABLE;
For the same year only you can do:
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME, EXTRACT(YEAR FROM LAST_DAY_MONTH) ORDER BY LAST_DAY_MONTH) AS HEADCOUNT_TO_DATE
FROM
TEST_TABLE;
答案2
得分: 0
我找到了解决方案，Asi Salo的答案指引了我正确的方向：
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME, EXTRACT(year FROM LAST_DAY_MONTH) ORDER BY EXTRACT(month FROM LAST_DAY_MONTH)) AS HEADCOUNT_TO_DATE
FROM `TEST_TABLE`
我找到了解决方案，Asi Salo的答案指引了我正确的方向：
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME, EXTRACT(year FROM LAST_DAY_MONTH) ORDER BY EXTRACT(month FROM LAST_DAY_MONTH)) AS HEADCOUNT_TO_DATE
FROM `TEST_TABLE`
英文:
I found the solution, the answer of Asi Salo pointed me in the right direction:
SELECT
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME, EXTRACT(year FROM LAST_DAY_MONTH) ORDER BY EXTRACT(month FROM LAST_DAY_MONTH)) AS HEADCOUNT_TO_DATE
FROM `TEST_TABLE`
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论