存储过程每15分钟分配一个区间并相加

最新推荐文章于 2024-04-25 02:21:39 发布

原创最新推荐文章于 2024-04-25 02:21:39 发布 · 411 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server

小知识同时被 3 个专栏收录

114 篇文章

订阅专栏

小

109 篇文章

订阅专栏

SQLServer

12 篇文章

订阅专栏

本文介绍了一种使用SQL进行批量时间区间统计的方法。通过创建临时表并对数据按小时和15分钟段进行分组，实现了对指定日期下拍卖数量的时间分布统计。此过程涉及多表联接和条件筛选，为数据分析师提供了一种实用的数据处理思路。

ALTER proc [dbo].[sp_hyfx_SoldOutDistributiones]
(@thedate bigint)
as
begin
-- create table #houres
--(houres int )
--declare @i int
--set @i=1
--while @i<=24
--begin
--insert #houres values (@i-1)
--set @i =@i+1
--end

select datepart( hour , OperationDate ) as houres, datepart( minute , OperationDate ) as minutess,AuctionNum, OperationDate ,thedate into #hhh from hyfx_SoldOutDistribution where thedate=@thedate

select '1-15' AS times , houres ,sum(AuctionNum) as AuctionNum into #tb1 from #hhh where thedate=@thedate AND MINUTESS BETWEEN 1 AND 15 group by houres
UNION
select '16-30' AS times , houres ,sum(AuctionNum) as AuctionNum from #hhh where thedate=@thedate AND MINUTESS BETWEEN 16 AND 30 group by houres
UNION
select '30-45' AS times , houres ,sum(AuctionNum) as AuctionNum from #hhh where thedate=@thedate AND MINUTESS BETWEEN 30 AND 45 group by houres
UNION
select '45-60' AS times , houres ,sum(AuctionNum) as AuctionNum from #hhh where thedate=@thedate AND MINUTESS BETWEEN 45 AND 60 group by houres
-- select T.houres ,cast( isnull( a.AuctionNum ,0) as varchar) +'+' +cast ( isnull(b.AuctionNum ,0) as varchar)+'+' +cast ( isnull(c.AuctionNum ,0) as varchar)+'+' +cast ( isnull(d.AuctionNum ,0) as varchar) as section
--from #houres t left join #tb1 a on t.houres=a.houres and a.times ='1-15' left join #tb1 b on t.houres =b.houres and b.times='16-30' left join #tb1 c on t.houres=c.houres and c.times='30-45' left join #tb1 d on t.houres=d.houres
--and d.times='45-60'

select distinct a.houres ,cast( isnull( a.AuctionNum ,0) as varchar) +'+' +cast ( isnull(b.AuctionNum ,0) as varchar)+'+' +cast ( isnull(c.AuctionNum ,0) as varchar)+'+' +cast ( isnull(d.AuctionNum ,0) as varchar) as section
from #tb1 a left join #tb1 b on a.houres =b.houres and b.times='16-30' left join #tb1 c on a.houres=c.houres and c.times='30-45' left join #tb1 d on a.houres=d.houres
and d.times='45-60' and a.times ='1-15'
--select * from #hhh
--select distinct a.houres ,cast( a.AuctionNum as varchar) +'+' +cast (b.AuctionNum as varchar)+'+' +cast (c.AuctionNum as varchar) +'+' +cast (d.AuctionNum as varchar) from #tb1 a ,#tb1 b ,#tb1 c,#tb1 d where a.houres =b.houres and a.houres=c.houres and a.houres=d.houres and a.times ='1-15' and b.times ='16-30' and c.times ='30-45' and d.times ='45-60'

end;

exec sp_hyfx_SoldOutDistributiones 20171018