一组相同数据的日期范围 [英] Date Range for set of same data

问题描述

我正在尝试构建一个 SQL 查询，该查询将为我提供价格相同的日期的日期范围.如果价格出现突破，我希望看到它出现在新的行列中.即使在一个月内的某个时间有相同的价格，如果价格在两者之间的某个时间发生变化，我也希望将其视为具有特定日期范围的两个单独的行.

I am trying to build a SQL query which will give me the date range for the dates with same prices. If there is a break in the prices, I expect to see it in a new line. Even if sometime during the month there are same prices, if there is change in the prices sometime in between I want to see it as two separate rows with the specific date range.

样本数据:

Date    Price
1-Jan   3.2
2-Jan   3.2
3-Jan   3.2
4-Jan   3.2
5-Jan   3.2
6-Jan   3.2
7-Jan   3.2
8-Jan   3.2
9-Jan   3.5
10-Jan  3.5
11-Jan  3.5
12-Jan  3.5
13-Jan  3.5
14-Jan  4.2
15-Jan  4.2
16-Jan  4.2
17-Jan  3.2
18-Jan  3.2
19-Jan  3.2
20-Jan  3.2
21-Jan  3.2
22-Jan  3
23-Jan  3
24-Jan  3
25-Jan  3
26-Jan  3
27-Jan  3
28-Jan  3
29-Jan  3.5
30-Jan  3.5
31-Jan  3.5

想要的结果:

Price   Date Range
3.2     1-8
3.5     9-13
4.2     14-16
3.2     17-22
3       22-28
3.5     29-31

推荐答案

非关系解决方案

我不认为任何其他答案是正确的.

Non-relational Solution

I don't think any of other answers are correct.

• GROUP BY 不起作用

使用ROW_NUMBER() 强制将数据放入物理记录归档系统结构中，然后将其作为物理记录处理.以巨大的性能成本.当然，为了编写这样的代码，它迫使您从 RFS 的角度思考，而不是从关系的角度思考.

Using ROW_NUMBER() forces the data into a Record Filing System structure, which is physical, and then processes it as physical records. At a massive performance cost. Of course, in order to write such code, it forces you to think in terms of RFS instead of thinking in Relational terms.

使用 CTE 是一样的.遍历数据，尤其是不会改变的数据.成本略有不同.

Using CTEs is the same. Iterating through the data, especially data that does not change. At a slightly different massive cost.

由于一系列不同的原因，游标绝对是错误的.(a) 游标需要代码，并且您请求了一个视图 (b) 游标放弃设置处理引擎，并恢复到逐行处理.再次，不需要.如果我的任何团队中的开发人员在关系数据库(即不是记录归档系统)上使用游标或临时表，我就会开枪.

Cursors are definitely the wrong thing for a different set of reasons. (a) Cursors require code, and you have requested a View (b) Cursors abandon the set-processing engine, and revert to row-by-row processing. Again, not required. If a developer on any of my teams uses cursors or temp tables on a Relational Database (ie. not a Record Filing System), I shoot them.

1. 您的数据是关系的、逻辑的，两个给定的数据列就是必需的.

1. Your data is Relational, logical, the two given data columns are all that is necessary.

当然，我们必须形成一个视图(派生关系)，以获得所需的报告，但它由纯 SELECT 组成，这与处理(将其转换为文件，这是物理的，然后处理文件；或临时表；或工作表；或 CTE；或 ROW_Number()；等).

Sure, we have to form a View (derived Relation), to obtain the desired report, but that consists of pure SELECTs, which is quite different to processing (converting it to a file, which is physical, and then processing the file; or temp tables; or worktables; or CTEs; or ROW_Number(); etc).

与有议程的理论家"的哀叹相反，SQL 可以完美地处理关系数据.而您的数据是关系数据.

Contrary to the lamentations of "theoreticians", who have an agenda, SQL handles Relational data perfectly well. And you data is Relational.

因此，请保持关系思维、数据的关系视图和集合处理思维.关系数据库上的每个报告要求都可以使用单个 SELECT 来满足.无需回归到 1970 年之前的 ISAM 文件处理方法.

Therefore, maintain a Relational mindset, a Relational view of the data, and a set-processing mentality. Every report requirement over a Relational Database can be fulfilled using a single SELECT. There is no need to regress to pre-1970 ISAM File handling methods.

我将假设主键(提供关系行唯一性的列集)是 Date, 并且根据给定的示例数据，数据类型是 DATE.

I will assume the Primary Key (the set of columns that give a Relational row uniqueness) is Date, and based on the example data given, the Datatype is DATE.

试试这个:

    CREATE VIEW MyTable_Base_V          -- Foundation View
    AS
        SELECT  Date,
                Date_Next,
                Price
            FROM (
            -- Derived Table: project rows with what we need
            SELECT  Date,
                    [Date_Next] = DATEADD( DD, 1, O.Date ),
                    Price,
                    [Price_Next] = (

                SELECT Price            -- NULL if not exists
                    FROM MyTable
                    WHERE Date = DATEADD( DD, 1, O.Date )
                    )

                FROM MyTable MT

                ) AS X
            WHERE Price != Price_Next   -- exclude unchanging rows
    GO

    CREATE VIEW MyTable_V               -- Requested View
    AS
        SELECT  [Date_From] = (
            --  Date of the previous row
            SELECT MAX( Date_Next )     -- previous row
                FROM MyTable_V
                WHERE Date_Next < MT.Date
                ),

                [Date_To] = Date,       -- this row
                Price
            FROM MyTable_Base_V MT
    GO

    SELECT  *
        FROM MyTable_V
    GO

方法，通用

当然这是一个方法，因此它是通用的，它可以用来确定任何数据范围的From_和To_(这里，一个日期范围)，基于任何数据变化(这里是Price的变化).

Method, Generic

Of course this is a method, therefore it is generic, it can be used to determine the From_ and To_ of any data range (here, a Date range), based on any data change (here, a change in Price).

这里，您的Dates 是连续的，因此Date_Next 的确定很简单:将Date 增加1 天.如果 PK 增加但不连续(例如 DateTime 或 TimeStamp 或其他一些键)，请更改派生表 X 到:

Here, your Dates are consecutive, so the determination of Date_Next is simple: increment the Date by 1 day. If the PK is increasing but not consecutive (eg. DateTime or TimeStamp or some other Key), change the Derived Table X to:

    -- Derived Table: project rows with what we need
    SELECT  DateTime,
            [DateTime_Next] = (
            -- first row > this row
        SELECT  TOP 1
                DateTime                -- NULL if not exists
            FROM MyTable
            WHERE DateTime > MT.DateTime
            ),

            Price,
            [Price_Next] = (
            -- first row > this row
        SELECT  TOP 1
                Price                   -- NULL if not exists
            FROM MyTable
            WHERE DateTime > MT.DateTime
            )

        FROM MyTable MT

享受.

请随时发表评论，提出问题等

Please feel free to comment, ask questions, etc.

这篇关于一组相同数据的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！