SQL计算从指定日期的第一天到指定日期的差异

转载作者：行者123 更新时间：2023-12-02 02:48:15

我正在处理 sql 查询以获取差异。我有一个包含读数、读数时间戳、id 的表。我的最终目标是获得三个差异。 1. 前一天读数与第二天读数之间的差异，2. 时间戳值前 7 天读数之间的差异，3. 从当月的第一天开始到每个指定日期的读数之间的差异。

我破解了前两项。现在我正在尝试破解第三个。我知道它会很容易使用功能，任何人都可以帮助我处理第三个请求。

预期结果:11 月 1 日的读数为 1000，11 月 2 日的读数为 1020，11 月 3 日的读数为 1050，11 月 2 日的差值应为 20，11 月 3 日的差值应为 50。

如果一个月的第一天没有数据，则取可用日期最少的数据。例如，september 只有从 24 开始，所以从 9 月 24 日开始阅读。

下面是示例表。

+----+-----------+---------+----------------+----------------+-----------------+
| id | timestamp | Reading | 1DayDifference | 7DayDifference | monthDifference |
+----+-----------+---------+----------------+----------------+-----------------+
| A1 | 11/20/18  |   44182 |              0 |            300 |             541 |
| A1 | 11/19/18  |   44182 |              0 |            338 |             541 |
| A1 | 11/18/18  |   44182 |              0 |            338 |             541 |
| A1 | 11/17/18  |   44182 |             38 |            338 |             541 |
| A1 | 11/16/18  |   44144 |            197 |            300 |             503 |
| A1 | 11/15/18  |   43947 |             26 |            103 |                 |
| A1 | 11/14/18  |   43921 |             39 |            158 |                 |
| A1 | 11/13/18  |   43882 |             38 |            158 |                 |
| A1 | 11/12/18  |   43844 |              0 |            120 |                 |
| A1 | 11/11/18  |   43844 |              0 |            120 |                 |
| A1 | 11/10/18  |   43844 |              0 |            160 |                 |
| A1 | 11/09/18  |   43844 |              0 |            203 |                 |
| A1 | 11/08/18  |   43844 |             81 |            241 |                 |
| A1 | 11/06/18  |   43763 |             39 |            198 |                 |
| A1 | 11/05/18  |   43724 |              0 |            198 |                 |
| A1 | 11/04/18  |   43724 |              0 |            198 |                 |
| A1 | 11/03/18  |   43724 |             40 |            198 |                 |
| A1 | 11/02/18  |   43684 |             43 |            199 |                 |
| A1 | 11/01/18  |   43641 |             38 |            194 |                 |
| A1 | 10/31/18  |   43603 |             38 |            275 |             237 |
| A1 | 10/30/18  |   43565 |             39 |            317 |                 |
| A1 | 10/29/18  |   43526 |              0 |            317 |                 |
| A1 | 10/28/18  |   43526 |              0 |            317 |                 |
| A1 | 10/27/18  |   43526 |             41 |            317 |                 |
| A1 | 10/26/18  |   43485 |             38 |            276 |                 |
| A1 | 10/25/18  |   43447 |            119 |            238 |                 |
| A1 | 10/24/18  |   43328 |             80 |            119 |                 |
+----+-----------+---------+----------------+----------------+-----------------+

我用于第一种两种类型的 SQL。

SELECT  id,
        timestamp,
        Reading,
        Reading - lead(Reading,1,0) OVER( partition BY [id] ORDER BY timestamp desc) [OneDayDifference],
        Reading - lead(Reading,7,0) OVER( partition BY [id] ORDER BY timestamp desc) [SevDayDifference]
FROM    [dbo].[test_example]     s
ORDER BY id, timestamp desc

下面是生成上述数据的脚本。

CREATE TABLE [dbo].[test_Example](
    [id] [nvarchar](50) NOT NULL,
    [timestamp] [datetime2](7) NOT NULL,
    [reading] [int] NOT NULL,
    [OneDayDifference] [int] NOT NULL,
    [SevDayDifference] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-19T00:01:38.0000000' AS DateTime2), 44182, 0, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-18T00:01:44.0000000' AS DateTime2), 44182, 0, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-17T00:01:35.0000000' AS DateTime2), 44182, 38, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-16T00:01:39.0000000' AS DateTime2), 44144, 197, 300)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-15T00:01:47.0000000' AS DateTime2), 43947, 26, 103)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-14T00:01:40.0000000' AS DateTime2), 43921, 39, 158)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-13T00:01:38.0000000' AS DateTime2), 43882, 38, 158)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-12T00:02:39.0000000' AS DateTime2), 43844, 0, 120)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-11T00:01:37.0000000' AS DateTime2), 43844, 0, 120)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-10T00:01:37.0000000' AS DateTime2), 43844, 0, 160)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-09T00:01:37.0000000' AS DateTime2), 43844, 0, 203)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-08T00:01:46.0000000' AS DateTime2), 43844, 81, 241)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-06T00:01:36.0000000' AS DateTime2), 43763, 39, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-05T00:02:27.0000000' AS DateTime2), 43724, 0, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-04T00:01:37.0000000' AS DateTime2), 43724, 0, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-03T00:01:48.0000000' AS DateTime2), 43724, 40, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-02T00:01:33.0000000' AS DateTime2), 43684, 43, 199)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-01T00:01:41.0000000' AS DateTime2), 43641, 38, 194)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-31T00:01:32.0000000' AS DateTime2), 43603, 38, 275)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-30T00:01:34.0000000' AS DateTime2), 43565, 39, 43565)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-29T00:02:45.0000000' AS DateTime2), 43526, 0, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-28T00:01:43.0000000' AS DateTime2), 43526, 0, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-27T00:01:31.0000000' AS DateTime2), 43526, 41, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-26T00:01:30.0000000' AS DateTime2), 43485, 38, 43485)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-25T00:01:35.0000000' AS DateTime2), 43447, 119, 43447)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-24T00:01:43.0000000' AS DateTime2), 43328, 43328, 43328)
GO

最佳答案

查找月份的第一天需要向后查看可变数量的行，因此而不是 LEAD()或 LAG()您可以在 apply 中使用相关子查询.请注意，因为您正在“向后看”，所以我更喜欢使用 LAG()而不是颠倒时间戳和LEAD()的顺序，但是两者都会产生相同的结果。

nb:此子查询将查找任何月份中最早的时间戳，如果不需要则添加 and t.timestamp < dateadd(dd,1,dateadd(mm,datediff(mm,0,s.timestamp),0))进入where子句

SELECT
    id
  , timestamp
  , Reading
  , Reading - LAG( Reading, 1, 0 ) OVER (PARTITION BY [id] ORDER BY timestamp) [OneDayDifference]
  , Reading - LAG( Reading, 7, 0 ) OVER (PARTITION BY [id] ORDER BY timestamp) [SevDayDifference]
  , reading - oa.prev_reading [ThisMonthDiff]
FROM [dbo].[test_example] s
outer apply (
    select top(1) t.reading prev_reading
    from [dbo].[test_example] t
    where s.id = t.id
    and t.timestamp >= dateadd(mm,datediff(mm,0,s.timestamp),0)
       -- and t.timestamp < dateadd(dd,1,dateadd(mm,datediff(mm,0,s.timestamp),0))
    order by t.timestamp
    ) oa
ORDER BY
    id
  , timestamp DESC
;

结果:

+----+----+------------+---------+------------------+------------------+---------------+
|    | id | timestamp  | Reading | OneDayDifference | SevDayDifference | ThisMonthDiff |
+----+----+------------+---------+------------------+------------------+---------------+
|  1 | A1 | 2018-11-19 |   44182 |                0 |              338 |           541 |
|  2 | A1 | 2018-11-18 |   44182 |                0 |              338 |           541 |
|  3 | A1 | 2018-11-17 |   44182 |               38 |              338 |           541 |
|  4 | A1 | 2018-11-16 |   44144 |              197 |              300 |           503 |
|  5 | A1 | 2018-11-15 |   43947 |               26 |              103 |           306 |
|  6 | A1 | 2018-11-14 |   43921 |               39 |              158 |           280 |
|  7 | A1 | 2018-11-13 |   43882 |               38 |              158 |           241 |
|  8 | A1 | 2018-11-12 |   43844 |                0 |              120 |           203 |
|  9 | A1 | 2018-11-11 |   43844 |                0 |              120 |           203 |
| 10 | A1 | 2018-11-10 |   43844 |                0 |              160 |           203 |
| 11 | A1 | 2018-11-09 |   43844 |                0 |              203 |           203 |
| 12 | A1 | 2018-11-08 |   43844 |               81 |              241 |           203 |
| 13 | A1 | 2018-11-06 |   43763 |               39 |              198 |           122 |
| 14 | A1 | 2018-11-05 |   43724 |                0 |              198 |            83 |
| 15 | A1 | 2018-11-04 |   43724 |                0 |              198 |            83 |
| 16 | A1 | 2018-11-03 |   43724 |               40 |              198 |            83 |
| 17 | A1 | 2018-11-02 |   43684 |               43 |              199 |            43 |
| 18 | A1 | 2018-11-01 |   43641 |               38 |              194 |             0 |
| 19 | A1 | 2018-10-31 |   43603 |               38 |              275 |           275 |
| 20 | A1 | 2018-10-30 |   43565 |               39 |            43565 |           237 |
| 21 | A1 | 2018-10-29 |   43526 |                0 |            43526 |           198 |
| 22 | A1 | 2018-10-28 |   43526 |                0 |            43526 |           198 |
| 23 | A1 | 2018-10-27 |   43526 |               41 |            43526 |           198 |
| 24 | A1 | 2018-10-26 |   43485 |               38 |            43485 |           157 |
| 25 | A1 | 2018-10-25 |   43447 |              119 |            43447 |           119 |
| 26 | A1 | 2018-10-24 |   43328 |            43328 |            43328 |             0 |
+----+----+------------+---------+------------------+------------------+---------------+

上面我用了outer apply这就像一个外连接(如果没有找到匹配的结果，源行仍然被返回)。如果这不是不必要的，那么使用 cross apply相反。

编辑

SELECT
    id
  , format(timestamp, 'yyyy-MM-dd') [timestamp]
  , Reading
  , COALESCE(Reading - LAG( Reading, 1) OVER (PARTITION BY [id] ORDER BY timestamp),0) [OneDayDifference]
  , COALESCE(Reading - LAG( Reading, 7) OVER (PARTITION BY [id] ORDER BY timestamp),0) [SevDayDifference]
  , reading - ca.tr [ThisMonthDiff]
FROM [dbo].[test_example] s
cross apply (
    select top(1) t.reading tr
    from [dbo].[test_example] t
    where s.id = t.id
    and t.timestamp >= dateadd(mm,datediff(mm,0,s.timestamp),0)
    order by t.timestamp
    ) ca
ORDER BY
    id
  , timestamp DESC
;

+----+----+------------+---------+------------------+------------------+---------------+
|    | id | timestamp  | Reading | OneDayDifference | SevDayDifference | ThisMonthDiff |
+----+----+------------+---------+------------------+------------------+---------------+
|  1 | A1 | 2018-11-19 |   44182 |                0 |              338 |           541 |
|  2 | A1 | 2018-11-18 |   44182 |                0 |              338 |           541 |
|  3 | A1 | 2018-11-17 |   44182 |               38 |              338 |           541 |

| 18 | A1 | 2018-11-01 |   43641 |               38 |              194 |             0 |
| 19 | A1 | 2018-10-31 |   43603 |               38 |              275 |           275 |
| 20 | A1 | 2018-10-30 |   43565 |               39 |                0 |           237 |
| 21 | A1 | 2018-10-29 |   43526 |                0 |                0 |           198 |
| 22 | A1 | 2018-10-28 |   43526 |                0 |                0 |           198 |
| 23 | A1 | 2018-10-27 |   43526 |               41 |                0 |           198 |
| 24 | A1 | 2018-10-26 |   43485 |               38 |                0 |           157 |
| 25 | A1 | 2018-10-25 |   43447 |              119 |                0 |           119 |
| 26 | A1 | 2018-10-24 |   43328 |                0 |                0 |             0 |
+----+----+------------+---------+------------------+------------------+---------------+

关于SQL计算从指定日期的第一天到指定日期的差异，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/53400286/

文章推荐： r - 标记 data.table 的某些列中具有相同行的组

文章推荐： r - 非常困惑如何合并两个图像来创建 gif

java - 指定 "other"
我正在我的 java 作业中使用 GUI，并且我必须指定 JCheckBox 中的其他内容。除了这个小要求，其他的我都完成了。我不太确定如何解决这个问题，我查阅了我的书并尝试在线研究要求: 一系列复
javascript - 指定 for 循环的终点有哪些优点和缺点？
在各种语言中(我将在这里使用 JavaScript，但我已经在 PHP 和 C++ 中以及可能在其他地方看到过它)，似乎有几种构造简单 for 循环的方法。版本 1 如下: var top = doc
javascript - css 指定 < >
有没有一种方法可以使用 CSS 指定每次“小于符号”(在键盘上 M 的右侧)或“大于符号”出现在文本中时，它应该被替换为分别是“小于”或“大于”的实际词？最佳答案 CSS 不能作用于(不能修改，即)
kerberos - 指定 SPN 的正确格式是什么？
首先，使用 setspn 命令为用户注册服务主体名称。 setspn -a CS/dummy@abc.com dummyuser setspn -l dummyuser 给出输出为 CS/dummy@
javascript - 指定 SFSafariViewController 用户代理
我在指定从 SFSafariViewController 访问时遇到问题，因为它具有与 Safari 浏览器完全相同的用户代理。我要做的是仅在 webview 内显示图片，如果在普通浏览器上查看，则
r - 指定 CFA，其中湍流是外生相关性的总和
我正在尝试用 R 语言在 lavaan 中指定一个奇怪的模型。该模型如下所示: 我的规范尝试如下所示。我发现难以实现的是将观察到的变量的唯一误差固定为唯一项的两个相关性的总和。例如，项目 y*1,2
reactjs - 指定 axios 响应数据类型
我正在构建 API 以将我的 React 应用程序与我的后端服务连接起来，我想使用 typescript 来指定 data 的类型在我的 Axios 请求中。如何在不修改其他字段的情况下更新 Axio
z3 - 指定 Z3 的初始模型值
如何为模型指定初始“软”值？该初始模型是解决类似查询的结果，并且该模型很可能具有正确的部分，甚至对于当前查询可能是正确的。目前，我正在通过增量求解和 hard/soft constraints 对此
java - 指定 Kafka 生产者的分区数
我有来自网页的以下代码 https://cwiki.apache.org/confluence/display/KAFKA/0.8.0+Producer+Example 似乎缺少的是如何配置分区数。我
Neo4jClient:指定 Cypher 解析器版本？
有没有办法在每个查询的基础上在 Neo4jClient 中指定 Cypher 解析器的版本，如 here 所述? 谢谢! 最佳答案如果您将 Neo4jClient 更新到最新版本(> 1.0.0.6
r - 指定 `curve` 绘图高度？
我有以下代码生成四个图，但它们最终被压扁(见下图)。我该如何解决这个问题？ par(mfrow=c(2,2)) curve(.5*exp(-.5*x),from=0,to=10,main="f(x)"
coldfusion - 指定 JDBC 数据库驱动程序最大线程数
我有一个 ColdFusion 10 服务器。我正在使用 JDBC 驱动程序连接到 db2 数据库。我偶然发现了这个笔记。这个设置在哪里？我还查看了 neo*.xml 文件，但没有看到任何 db 驱动
jquery - 指定 jQuery 验证插件中验证器的顺序
我想知道是否可以指定验证器的运行顺序。目前，我编写了一个自定义验证器，检查它是否为 [a-zA-Z0-9]+ 以确保登录验证我们的规则，并编写了一个远程验证器以确保登录可用，但目前远程验证器已启动在
iphone - 指定 iPhone 应用程序的最低内存要求
我的应用程序需要至少 40MB 的 RAM，因此早期的 iPhone(例如 3G、第一个 iPod touch 版本)就没有它(它们为我的应用程序提供的最大内存约为 20MB)。有没有正确的方法来禁用
java - 指定 ZonedDateTime 的时区而不更改实际日期
我有一个保存日期(不是当前日期)的 Date 对象，我需要以某种方式指定该日期为 UTC，然后将其转换为“欧洲/巴黎”，即 +1 小时。 public static LocalDateTime toL
caching - 指定 varnish 后端而不缓存
我想问你在 Varnish 代码中如何在没有缓存的情况下将请求传递到后端。我知道我可以做到并且正在发挥作用: if (req.url ~ "(\?|&)(something|somethin
module - 指定 gfortran 应在其中查找模块的目录
我目前基于模块编译程序(如主程序 foo 依赖于模块 bar )如下: gfortran -c bar.f90 gfortran -o foo.exe foo.f90 bar.o 这在 foo.f90
javascript - 指定 Meteor 包依赖项的正确方法
我正在尝试创建一个依赖于另一个 meteor 包的新 meteor 包。当我尝试 meteor add mypackage 时，出现以下错误。为什么 Meteor 不添加 mypackage 并引入它
rust - 指定 Rust 闭包生命周期
我正在制作执行器/ react 器，同时发现这是一个终生的问题。它与 async/Future 无关，可以在没有 async 糖的情况下进行复制。 use std::future::Future; s
cassandra - 指定 cqlsh 输出时区
我在 cassandra 中有一个表，其数据类型为时间戳。我正在使用 cqlsh 从数据库中获取数据，并希望更改我的时间戳列输出的输出格式。我研究了一下，发现我可以通过更改以下文件来更改时间戳输出格式

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

SQL计算从指定日期的第一天到指定日期的差异

编辑