通过分组列的值，以便改变组数据(Group data by the change of groupi

用下面的数据

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

所以基本上就是我希望做的是通过基于组的列的产品和价格数据的步骤更改组。

什么是实现这一目标的最彻底的方法？

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     

Answer 2:

我是新来这个论坛，所以希望我的贡献是很有帮助的。

如果你真的不想使用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个记录偏移（+或 - ），我们可以当价格桶改变，那么它刚刚在桶中的开始和结束日期回到一个有案可查的标识。

一切有点繁琐，我不知道它要提供更好的性能，但我喜欢挑战。

Answer 3:

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

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万行的表。

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

文章来源: Group data by the change of grouping column value in order