SQL中如何按日期范围分组
借鉴 StackOverflow.com 的这篇其他帖子我发现这很有用。
我想知道是否可以使用我未明确定义的日期范围进行分组。
为了澄清起见，假设我有一个名为“人员”的表和一个名为“出生日期”的列。
我想对一个时期内出生的人数进行分组和统计。
所以它看起来像这样。
出生计数
Mar1980 25
Apr1980 452
May1980 42
...
Dec1993 452
该期间可以是几周、几个月和几个季度。
如果我使用其他解决方案，我需要指定每个日期范围，即使是月份和年份，也需要很长时间，因为人们的出生日期差异很大。
select t.DOB as [dob], count(*) as [number of occurences]
from (
select user_id,
case when date >= '1980-01-01' and date < '1980-02-01 then 'Jan 1980'
when date >= '1980-02-01' and date < '1980-03-01 then 'Feb 1980'
...
when date >= '1990-03-01' and date < '1990-04-01 then 'Mar 1990'
else 'Null' end as DOB
from Person) t
group by t.DOB
您能想到有什么解决方案可以让我指定数据范围的间隔大小并按这些日期范围进行分组吗？
Drawing on from this other post of StackOverflow.com which I found useful.
I was wondering if it is possible to group by using date ranges that I don't explicitly define.
So to clarify, say I have a table called Person and a column called Date Of Birth.
I would like to group by and count the number of people born in a period.
So it would look something like this.
Born Count
Mar1980 25
Apr1980 452
May1980 42
...
Dec1993 452
The period could be weeks, months and quarters.
If I used the other solution, I would need to specify every single date range, even with months and years, it would take a long time as people's date of birth vary a lot.
select t.DOB as [dob], count(*) as [number of occurences]
from (
select user_id,
case when date >= '1980-01-01' and date < '1980-02-01 then 'Jan 1980'
when date >= '1980-02-01' and date < '1980-03-01 then 'Feb 1980'
...
when date >= '1990-03-01' and date < '1990-04-01 then 'Mar 1990'
else 'Null' end as DOB
from Person) t
group by t.DOB
Is there any solution you can think of that can allow me to specify just the size of the interval I want the data ranges to be and group by these date ranges?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
像这样的东西能满足您的需求吗？诀窍是使用和滥用 CONVERT 函数来获取您的时间段很好地结合在一起，然后使用 DENSE_RANK将其简化为单调递增序列。
）
SELECT * FROM ROLLUPS R
使用上面的内容，我得到如下输出
Would something like this address what you're after? The trick is to use and abuse the CONVERT function to get your time periods to roll together nicely and then use DENSE_RANK to reduce it to a monotonically increase sequence .
)
SELECT * FROM ROLLUPS R
Using the above, I get output like
您可能可以使用类似的东西并利用函数 DATEPART
。不要忘记取消注释您要测试的语句。
You could probably use something like that and take advantage of function DATEPART
Please. don't forget to uncomment the statement you want to test.