sql - 쿼리란 - 크로스탭이란




순서대로 그룹화 열 값을 변경하여 데이터 그룹화 (4)

나는이 포럼에 처음 왔으므로 나의 기여가 도움이되기를 바랍니다.

정말로 CTE를 사용하고 싶지 않다면 (아마도 가장 좋은 접근 방식이라고 생각하지만) set 기반 코드를 사용하여 솔루션을 얻을 수 있습니다. 이 코드의 성능을 테스트해야합니다.

여분의 임시 테이블에 추가하여 각 레코드에 대해 고유 한 식별자를 사용할 수 있지만 소스 테이블에이 열이 이미있을 것입니다. 그래서 임시 테이블을 heres.

    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 레코드 (+ 또는 -)로 묶음으로써 가격 버킷이 언제 바뀌는 지 파악하고 양동이의 시작 날짜와 끝 날짜를 단일 레코드로 다시 가져올 수 있습니다.

약간의 피칭과 나는 그것의 좋은 성능을 줄 수 있을지 모르겠지만 나는 도전을 즐겼다.

다음 데이터로

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

그래서 본질적으로 그룹 컬럼 제품 및 가격을 기반으로 한 데이터의 단계별 변경으로 그룹을 작성합니다.

이것을 달성하는 가장 깨끗한 방법은 무엇입니까?


비교적 깨끗한 해결책은 다음과 같습니다.

;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] 

row_number 와 함께 CTE를 사용 dateadd . dateadd 와 같은 함수를 사용하면 날짜가 누락되었는지 여부를 걱정할 필요가 없기 때문입니다. date_to 열 (내가하는)을 원한다면 분명히 외부 적용 만 필요합니다.

이 솔루션은 내 문제를 해결하지만 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

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




gaps-and-islands