SQL 查询 - 创建时间序列（从 2 列创建序列）

发布于 2025-01-16 07:02:00 字数 1171 浏览 2 评论 0原文

作为一名 SQL 新手，你能帮帮我吗？

在 SQL（或 Google 的 BigQuery）当前表中：

Column A	Column B
2021-12-31	null
null	2022-01-23
2009-04-12	2022-07-23

我的查询是错误的：

select
count(ColA)   as A,
count(ColB) as B,
FORMAT_DATE('%Y %m', ColA) as month_year,

from myTable
group by month_year

所以我到目前为止：

Month_year	Count A	Count B
2021-12	1	0

我想做一个简单的计数，聚合年份的输出月。正如您所看到的，由于我们使用的是 ColA 中的日期“数组”，因此我将丢失 B 栏的一些计数。

那么，如何对组合为年月分组的“聚合数组”的两列进行正确的计数。

我想要：

month_year	“计数”A	“计数”B
2009-04	1	0
2021-12	1	0
2022-01	0	1
2022-07	0	1

原文

As a n00b in SQL, can you help me out.

In SQL (or Google's BigQuery) current table :

Column A	Column B
2021-12-31	null
null	2022-01-23
2009-04-12	2022-07-23

My query which is wrong:

select
count(ColA)   as A,
count(ColB) as B,
FORMAT_DATE('%Y %m', ColA) as month_year,

from myTable
group by month_year

so i've got so far :

month_year	Count A	Count B
2021-12	1	0

the output i'd like to do a simple count, aggregated year month. As you can see i will be missing some counts on col B since we are using the 'array' of dates from ColA.

So how can I make a proper count on both columns combined as the 'aggregate array' for the year month grouping.

i want :

month_year	"Count" A	"Count" B
2009-04	1	0
2021-12	1	0
2022-01	0	1
2022-07	0	1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伴梦长久 2025-01-23 07:02:00

请尝试以下操作：

select FORMAT_DATE('%Y-%m', coalesce(column_a, column_b)) month_year
    , count(column_a) as a
    , count(column_b) as b
from sample_data
group by month_year

合并函数允许您返回一个不为空的值。需要注意的是，如果两列都具有不同月份的值，则此解决方案可能无法按预期工作。

Try the following:

select FORMAT_DATE('%Y-%m', coalesce(column_a, column_b)) month_year
    , count(column_a) as a
    , count(column_b) as b
from sample_data
group by month_year

The coalesce function allows you to return a value that is not null. As a caveat this solution may not work as expected if both columns have a value for both columns that are in different months.

回复收藏 0 原文

浴红衣 2025-01-23 07:02:00

您可以尝试这个（MS SQL）

   select
    COALESCE((FORMAT ( ColA , 'yyyy-MM' )), (FORMAT ( ColB , 'yyyy-MM' ))) AS month_year,
    count(ColA)   AS [Count A],
    count(ColB) AS [Count B]
    from myTable
    group by ColA,ColB

演示链接 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0fe2e9995a7040f8aaeecd8339012ee3

You can try this (MS SQL)

   select
    COALESCE((FORMAT ( ColA , 'yyyy-MM' )), (FORMAT ( ColB , 'yyyy-MM' ))) AS month_year,
    count(ColA)   AS [Count A],
    count(ColB) AS [Count B]
    from myTable
    group by ColA,ColB

DEMO LINK https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0fe2e9995a7040f8aaeecd8339012ee3

回复收藏 0 原文

云仙小弟 2025-01-23 07:02:00

请考虑以下方法

select * from (
  select format_date('%Y-%m', date(date)) month_year, column 
  from your_table
  unpivot (date for column in (columnA, columnB))
)
pivot (count(*) count for column in ('columnA', 'columnB'))

如果应用于问题

with your_table as (
  select '2021-12-31' columnA, null columnB union all
  select null,  '2022-01-23' union all
  select '2009-04-12', '2022-07-23'
)

输出中的示例数据，

Consider below approach

select * from (
  select format_date('%Y-%m', date(date)) month_year, column 
  from your_table
  unpivot (date for column in (columnA, columnB))
)
pivot (count(*) count for column in ('columnA', 'columnB'))

if applied to sample data in your question

with your_table as (
  select '2021-12-31' columnA, null columnB union all
  select null,  '2022-01-23' union all
  select '2009-04-12', '2022-07-23'
)

output is