按照分组列值的变化对数据进行分组排序

Question

有以下数据

create table #ph (product int, [date] date, price int)
insert into #ph select 1, '20120101', 1
insert into #ph select 1, '20120102', 1
insert into #ph select 1, '20120103', 1
insert into #ph select 1, '20120104', 1
insert into #ph select 1, '20120105', 2
insert into #ph select 1, '20120106', 2
insert into #ph select 1, '20120107', 2
insert into #ph select 1, '20120108', 2
insert into #ph select 1, '20120109', 1
insert into #ph select 1, '20120110', 1
insert into #ph select 1, '20120111', 1
insert into #ph select 1, '20120112', 1

我想要产生以下输出：

product | date_from | date_to  | price
  1     | 20120101  | 20120105 |   1
  1     | 20120105  | 20120109 |   2
  1     | 20120109  | 20120112 |   1

如果按价格分组并显示最大和最小日期，那么我会得到以下结果，这不是我想要的（请参见日期重叠）。

product | date_from | date_to  | price
  1     | 20120101  | 20120112 |   1
  1     | 20120105  | 20120108 |   2

实际上，我想要做的是根据产品和价格组合列中的数据变化步骤进行分组。

最简洁的方法是什么？

- MrEdmundo

---

这是所谓的“间隙和岛屿”问题的一个实例，供参考。 - AakashM

@AakashM会看一下，我尝试搜索了一下，但是没有找到这个问题的明确定义。谢谢。 - MrEdmundo

拥有一个“数字表”（在这种情况下是一个“日期表”）将极大地帮助。 - AakashM

Answer 1

解决这种问题有一个（或多或少）被认为已知的技巧，涉及两个ROW_NUMBER()调用，像这样：

WITH marked AS (
  SELECT
    *,
    grp = ROW_NUMBER() OVER (PARTITION BY product        ORDER BY date)
        - ROW_NUMBER() OVER (PARTITION BY product, price ORDER BY date)
  FROM #ph
)
SELECT
  product,
  date_from = MIN(date),
  date_to   = MAX(date),
  price
FROM marked
GROUP BY
  product,
  price,
  grp
ORDER BY
  product,
  MIN(date)

输出：

product  date_from   date_to        price 
-------  ----------  -------------  ----- 
1        2012-01-01  2012-01-04     1     
1        2012-01-05  2012-01-08     2     
1        2012-01-09  2012-01-12     1     

- Andriy M

---

谢谢，我刚刚看了一下我实现的内容，它是相同的，但我使用了两个不同的CTE，我没有想到在一个CTE中使用减法。谢谢。 - MrEdmundo

@andriy，有没有更优化的方法来做这件事？ - eshirvana

@eshirvana：有其他选项，但很难知道它们是否更适合您。我能想到的是，使用CASE + LAG()标记行并将1和0应用于该列，然后应用SUM() OVER方法。浏览gaps-and-islands标签以获取现成的解决方案，可以在SO或DBA.SE上找到。当然，您也可以尝试提交自己的问题。 - Andriy M

Answer 2

WITH marked AS (
  SELECT
    *,
  case
   when (lag(price,1,'') over (partition by product order by date_from)) = price
   then 0 else 1
  end is_price_change
  FROM #ph
),
marked_as_group AS
( SELECT m.*,
       SUM(is_price_change) over (PARTITION BY product order by date_from ROWS 
      BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS price_change_group
  FROM marked m
),
SELECT
  product,
  date_from = MIN(date_from),
  date_to   = MAX(date_to),
  price = MIN(price)
FROM marked_as_group 
GROUP BY
  product,
  price_change_group
ORDER BY
  product,
  date_to

- zhao Mingli

---

我发布这个解决方案是因为我遇到了类似的问题，但是当我应用Andriy M的解决方案时出现了一些错误。 - zhao Mingli

Answer 3

我是新来的，希望我的贡献有所帮助。

如果您真的不想使用CTE（尽管我认为这可能是最好的方法），您可以使用基于集合的代码来解决问题。您需要测试此代码的性能！

我添加了一个额外的临时表，以便我可以为每个记录使用唯一标识符，但我怀疑您已经在源表中拥有此列。因此，这是临时表。

    If Exists (SELECT Name FROM tempdb.sys.tables WHERE name LIKE '#phwithId%')
        DROP TABLE #phwithId    

    CREATE TABLE #phwithId
    (
        SaleId INT
        , ProductID INT
        , Price Money
        , SaleDate Date 
    )
    INSERT INTO #phwithId SELECT row_number() over(partition by product order by [date] asc) as SalesId, Product, Price, Date FROM ph 

现在是 Select 语句的主体部分。

    SELECT 
        productId 
        , date_from
        , date_to
        , Price
    FROM
        (   
            SELECT 
                dfr.ProductId
                , ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY ChangeDate) AS rowno1          
                , ChangeDate AS date_from
                , dfr.Price
            FROM
                (       
                    SELECT
                        sl1.ProductId AS ProductId
                        , sl1.SaleDate AS ChangeDate
                        , sl1.price
                    FROM
                        #phwithId sl1
                    LEFT JOIN
                        #phwithId sl2
                        ON sl1.SaleId = sl2.SaleId + 1
                    WHERE
                        sl1.Price <> sl2.Price OR sl2.Price IS NULL
                ) dfr
        ) da1
    LEFT JOIN
        (   
            SELECT 
                ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY ChangeDate) AS rowno2
                , ChangeDate AS date_to     
            FROM
                (   
                    SELECT 
                        sl1.ProductId
                        , sl1.SaleDate AS ChangeDate
                    FROM
                        #phwithId sl1
                    LEFT JOIN
                        #phwithId sl3
                        ON sl1.SaleId = sl3.SaleId - 1  
                    WHERE
                        sl1.Price <> sl3.Price OR sl3.Price IS NULL         
                ) dto

        ) da2 
        ON da1.rowno1 = da2.rowno2  

通过将数据源偏移1个记录(+或-)绑定，我们可以确定价格桶何时更改，然后只需将桶的开始和结束日期获取回单个记录即可。

这有点繁琐，我不确定它是否会提高性能，但我享受这个挑战。

- redsevi

Answer 4

我想到了一个相对“简洁”的解决方案：

;with cte_sort (product, [date], price, [row])
as
    (select product, [date], price, row_number() over(partition by product order by [date] asc) as row
     from #ph)

select a.product, a.[date] as date_from, c.[date] as date_to, a.price 
from cte_sort a
left outer join cte_sort b on a.product = b.product and (a.row+1) = b.row and a.price = b.price
outer apply (select top 1 [date] from cte_sort z where z.product = a.product and z.row > a.row order by z.row) c
where b.row is null
order by a.[date] 

我使用了CTE和row_number，因为如果你使用像dateadd这样的函数，那么你就不需要担心是否缺少任何日期。如果你想要date_to列（我需要），那么显然只需要外部应用。

这个解决方案确实解决了我的问题，但是在我的500万行表格上执行速度稍微有点慢。

- MrEdmundo

Answer 5

Create function [dbo].[AF_TableColumns](@table_name nvarchar(55))
returns nvarchar(4000) as
begin
declare @str nvarchar(4000)
    select @str = cast(rtrim(ltrim(column_name)) as nvarchar(500)) + coalesce('         ' + @str , '            ') 
    from information_schema.columns
    where table_name = @table_name
    group by table_name, column_name, ordinal_position 
    order by ordinal_position DESC
return @str
end

--select dbo.AF_TableColumns('YourTable') Select * from YourTable

- Jean Paul Nasab