sql - 通过动态参数按时间分组
问题描述
再会。我正在尝试执行一个查询,该查询基于一个动态参数的间隔来获取数据和组,该间隔是 30-300 秒的动态参数。如果参数是 45,那么我希望数据按 45 秒分组。
数据以每 30 秒的速度输入。我的查询没有正确分隔日期时间字段。我应该舍入日期吗?我正在使用该DATEPART
函数并除以动态参数。这是我应该这样做的方式吗?非常感谢任何帮助。
询问
DECLARE @DStart DATETIME
DECLARE @DStop DATETIME
DECLARE @printInt INT
SET @DStart = (SELECT TOP 1 Dateadd(millisecond, -250, dates)
FROM tablea
WHERE triggerval = 1
AND recipenumber = 136--@SelectRecipe
ORDER BY dates ASC)
SET @DStop = (SELECT TOP 1 dates
FROM tablea
WHERE triggerval = 0
AND recipenumber = 136--@SelectRecipe
ORDER BY dates DESC)
DECLARE @tbl8 TABLE
(
processval NVARCHAR(255) NULL,
processvariablename NVARCHAR(255) NULL,
dateentered DATETIME NULL,
vcheck INT NULL
);
INSERT INTO @tbl8
SELECT processval,
b.processvariablename,
a.dateentered,
0 AS vCheck
FROM procv a
INNER JOIN procvname b
ON a.numid = b.numid
WHERE a.dateentered >= @DStart
AND a.dateentered <= @DStop
AND b.isvisible > 0
GROUP BY Datepart(second, 0, dateentered) / @printInt,
dateentered,
processval,
processvariablename
SELECT *
FROM @tbl8
原始样本数据
0 2018-07-26 12:47:18.480
14.7514 2018-07-26 12:47:18.480
26.7243 2018-07-26 12:47:18.480
27.2616 2018-07-26 12:47:18.480
60.8281 2018-07-26 12:47:18.480
60.9732 2018-07-26 12:47:18.480
63.4687 2018-07-26 12:47:18.480
68.0573 2018-07-26 12:47:18.480
0 2018-07-26 12:47:48.213
14.7555 2018-07-26 12:47:48.213
26.7345 2018-07-26 12:47:48.213
27.2591 2018-07-26 12:47:48.213
60.8154 2018-07-26 12:47:48.213
60.9401 2018-07-26 12:47:48.213
63.4356 2018-07-26 12:47:48.213
68.1057 2018-07-26 12:47:48.213
0 2018-07-26 12:47:48.483
14.7555 2018-07-26 12:47:48.483
26.7345 2018-07-26 12:47:48.483
27.2591 2018-07-26 12:47:48.483
60.8154 2018-07-26 12:47:48.483
60.9401 2018-07-26 12:47:48.483
63.4356 2018-07-26 12:47:48.483
68.1057 2018-07-26 12:47:48.483
0 2018-07-26 12:48:18.483
21.1506 2018-07-26 12:48:18.483
27.0706 2018-07-26 12:48:18.483
27.1649 2018-07-26 12:48:18.483
61.3094 2018-07-26 12:48:18.483
61.8161 2018-07-26 12:48:18.483
64.7775 2018-07-26 12:48:18.483
70.0078 2018-07-26 12:48:18.483
0 2018-07-26 12:48:48.487
22.8971 2018-07-26 12:48:48.487
26.9688 2018-07-26 12:48:48.487
27.0859 2018-07-26 12:48:48.487
67.7925 2018-07-26 12:48:48.487
68.5385 2018-07-26 12:48:48.487
70.9347 2018-07-26 12:48:48.487
72.6892 2018-07-26 12:48:48.487
0 2018-07-26 12:49:18.487
23.5001 2018-07-26 12:49:18.487
26.9102 2018-07-26 12:49:18.487
30.5184 2018-07-26 12:49:18.487
68.867 2018-07-26 12:49:18.487
69.906 2018-07-26 12:49:18.487
71.5178 2018-07-26 12:49:18.487
84.3287 2018-07-26 12:49:18.487
0 2018-07-26 12:49:48.490
23.9836 2018-07-26 12:49:48.490
26.844 2018-07-26 12:49:48.490
36.8819 2018-07-26 12:49:48.490
71.0493 2018-07-26 12:49:48.490
71.9634 2018-07-26 12:49:48.490
73.7739 2018-07-26 12:49:48.490
93.7401 2018-07-26 12:49:48.490
0 2018-07-26 12:50:18.490
24.6327 2018-07-26 12:50:18.490
26.8567 2018-07-26 12:50:18.490
44.1773 2018-07-26 12:50:18.490
73.4811 2018-07-26 12:50:18.490
74.3621 2018-07-26 12:50:18.490
76.1497 2018-07-26 12:50:18.490
99.4975 2018-07-26 12:50:18.490
解决方案
如果对动态创建日期/时间范围的辅助函数开放,请考虑以下
您可以传递所需的日期部分(YY、QQ、MM、WK、DD、HH、MI、SS)和增量
例子
Declare @YourTable Table ([SomeValue] Money,[SomeDateTime] datetime)
Insert Into @YourTable Values
(0,'2018-07-26 12:47:18.480'),(14.7514,'2018-07-26 12:47:18.480'),(26.7243,'2018-07-26 12:47:18.480'),(27.2616,'2018-07-26 12:47:18.480'),(60.8281,'2018-07-26 12:47:18.480'),(60.9732,'2018-07-26 12:47:18.480'),(63.4687,'2018-07-26 12:47:18.480'),(68.0573,'2018-07-26 12:47:18.480')
,(0,'2018-07-26 12:47:48.213'),(14.7555,'2018-07-26 12:47:48.213'),(26.7345,'2018-07-26 12:47:48.213'),(27.2591,'2018-07-26 12:47:48.213'),(60.8154,'2018-07-26 12:47:48.213'),(60.9401,'2018-07-26 12:47:48.213'),(63.4356,'2018-07-26 12:47:48.213'),(68.1057,'2018-07-26 12:47:48.213')
,(0,'2018-07-26 12:47:48.483'),(14.7555,'2018-07-26 12:47:48.483'),(26.7345,'2018-07-26 12:47:48.483'),(27.2591,'2018-07-26 12:47:48.483'),(60.8154,'2018-07-26 12:47:48.483'),(60.9401,'2018-07-26 12:47:48.483'),(63.4356,'2018-07-26 12:47:48.483'),(68.1057,'2018-07-26 12:47:48.483')
,(0,'2018-07-26 12:48:18.483'),(21.1506,'2018-07-26 12:48:18.483'),(27.0706,'2018-07-26 12:48:18.483'),(27.1649,'2018-07-26 12:48:18.483'),(61.3094,'2018-07-26 12:48:18.483'),(61.8161,'2018-07-26 12:48:18.483'),(64.7775,'2018-07-26 12:48:18.483'),(70.0078,'2018-07-26 12:48:18.483')
,(0,'2018-07-26 12:48:48.487'),(22.8971,'2018-07-26 12:48:48.487'),(26.9688,'2018-07-26 12:48:48.487'),(27.0859,'2018-07-26 12:48:48.487'),(67.7925,'2018-07-26 12:48:48.487'),(68.5385,'2018-07-26 12:48:48.487'),(70.9347,'2018-07-26 12:48:48.487'),(72.6892,'2018-07-26 12:48:48.487')
,(0,'2018-07-26 12:49:18.487'),(23.5001,'2018-07-26 12:49:18.487'),(26.9102,'2018-07-26 12:49:18.487'),(30.5184,'2018-07-26 12:49:18.487'),(68.867,'2018-07-26 12:49:18.487'),(69.906,'2018-07-26 12:49:18.487'),(71.5178,'2018-07-26 12:49:18.487'),(84.3287,'2018-07-26 12:49:18.487')
,(0,'2018-07-26 12:49:48.490'),(23.9836,'2018-07-26 12:49:48.490'),(26.844,'2018-07-26 12:49:48.490'),(36.8819,'2018-07-26 12:49:48.490'),(71.0493,'2018-07-26 12:49:48.490'),(71.9634,'2018-07-26 12:49:48.490'),(73.7739,'2018-07-26 12:49:48.490'),(93.7401,'2018-07-26 12:49:48.490')
,(0,'2018-07-26 12:50:18.490'),(24.6327,'2018-07-26 12:50:18.490'),(26.8567,'2018-07-26 12:50:18.490'),(44.1773,'2018-07-26 12:50:18.490'),(73.4811,'2018-07-26 12:50:18.490'),(74.3621,'2018-07-26 12:50:18.490'),(76.1497,'2018-07-26 12:50:18.490'),(99.4975,'2018-07-26 12:50:18.490')
Select STime = B.RetVal1
,ETime = B.RetVal2
,RecCnt = sum(1)
,SumVal = sum(SomeValue)
,MinVal = min(NullIf(SomeValue,0))
,MaxVal = max(NullIf(SomeValue,0))
,AvgVal = avg(NullIf(SomeValue,0))
From @YourTable A
Join [dbo].[tvf-Range-Date-Span]('2018-07-26','2018-07-27','SS',30) B on SomeDateTime>=RetVal1 and SomeDateTime<RetVal2
Group By B.RetVal1,B.RetVal2
退货
感兴趣的功能
CREATE FUNCTION [dbo].[tvf-Range-Date-Span] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
with cte0(M) As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d,cte1 e,cte1 f,cte1 g,cte1 h ),
cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY,N*@Incr,@R1) When 'QQ' then DateAdd(QQ,N*@Incr,@R1) When 'MM' then DateAdd(MM,N*@Incr,@R1) When 'WK' then DateAdd(WK,N*@Incr,@R1) When 'DD' then DateAdd(DD,N*@Incr,@R1) When 'HH' then DateAdd(HH,N*@Incr,@R1) When 'MI' then DateAdd(MI,N*@Incr,@R1) When 'SS' then DateAdd(SS,N*@Incr,@R1) End From cte2 )
Select RetSeq = N+1
,RetVal1 = D
,RetVal2 = LEAD(D,1,@R2) over (Order By D)
From cte3,cte0
Where N<cte0.M-1
)
--Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
--Select * from [dbo].[tvf-Range-Date-Span]('2016-10-01','2020-10-01','YY',1)
如果它有助于可视化...
Select * From [dbo].[tvf-Range-Date-Span]('2018-07-26','2018-07-27','SS',30)
退货
RetSeq RetVal1 RetVal2
1 2018-07-26 00:00:00.000 2018-07-26 00:00:30.000
2 2018-07-26 00:00:30.000 2018-07-26 00:01:00.000
3 2018-07-26 00:01:00.000 2018-07-26 00:01:30.000
4 2018-07-26 00:01:30.000 2018-07-26 00:02:00.000
5 2018-07-26 00:02:00.000 2018-07-26 00:02:30.000
....
2878 2018-07-26 23:58:30.000 2018-07-26 23:59:00.000
2879 2018-07-26 23:59:00.000 2018-07-26 23:59:30.000
2880 2018-07-26 23:59:30.000 2018-07-27 00:00:00.000
推荐阅读
- python - 继续收到错误:'urllib.error.HTTPError:HTTP 错误 400:错误请求'
- kubernetes - 优先选择某些节点直到满
- php - 如何在登录该人之前使我的recaptcha需要
- mysql - 在查询中生成具有给定行数的 SQL 查询
- pygame - 在 pygame 中,密钥读取被延迟
- javascript - 在 Chrome Devtools 中监听 CSS 类的变化
- c - 在释放所有变量之前检查是否为空,但仍然出现内存错误
- r - 闪亮 - 如何显示数据框?
- google-apps-script - 工作表和日历之间的 Apps 脚本问题
- reactjs - 具有相似 url 的链接的 activeClassName