T-SQL多重分组

Question

我有以下数据：

Product Price   StartDate                   EndDate
Apples  4.9     2010-03-01 00:00:00.000     2010-03-01 00:00:00.000
Apples  4.9     2010-03-02 00:00:00.000     2010-03-02 00:00:00.000
Apples  2.5     2010-03-03 00:00:00.000     2010-03-03 00:00:00.000
Apples  4.9     2010-03-05 00:00:00.000     2010-03-05 00:00:00.000
Apples  4.9     2010-03-06 00:00:00.000     2010-03-06 00:00:00.000
Apples  4.9     2010-03-09 00:00:00.000     2010-03-09 00:00:00.000
Apples  2.5     2010-03-10 00:00:00.000     2010-03-10 00:00:00.000
Apples  4.9     2010-03-11 00:00:00.000     2010-03-11 00:00:00.000
Apples  4.9     2010-03-12 00:00:00.000     2010-03-12 00:00:00.000
Apples  4.9     2010-03-13 00:00:00.000     2010-03-13 00:00:00.000
Apples  4.9     2010-03-15 00:00:00.000     2010-03-15 00:00:00.000
Apples  4.9     2010-03-16 00:00:00.000     2010-03-16 00:00:00.000

希望能够按照类似于 产品、价格、最小开始日期、最大开始日期 的方式进行分组，但同时也需要按照开始日期和结束日期进行分组……就像下面这样的：

期望的结果：

Apples  4.9     2010-03-01 00:00:00.000     2010-03-02 00:00:00.000
Apples  2.5     2010-03-03 00:00:00.000     2010-03-03 00:00:00.000
Apples  4.9     2010-03-05 00:00:00.000     2010-03-09 00:00:00.000
Apples  2.5     2010-03-10 00:00:00.000     2010-03-10 00:00:00.000
Apples  4.9     2010-03-11 00:00:00.000     2010-03-16 00:00:00.000

- MayankBirthariya

---

欢迎来到StackOverflow：如果您发布代码、XML或数据样本，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码示例”按钮（{}）以使其格式化和语法高亮！ - marc_s

EndDate列的目的是什么？它似乎总是等于StartDate。这个假设是否正确？如果是，请从您的示例数据中删除EndDate列。如果不是真的，我希望你能提供最具“棘手性”的数据，而不是最统一/无聊的数据，以便提供答案的人可以确定正确的查询以始终提供正确的结果。 - ErikE

所以只是为了明确起见：即使你在2010-03-14没有显示任何数据，你还是想要看到Apples的最后一行，跨度为2010-03-11到20100316？ - ErikE

Answer 1

SELECT  product, price, MIN(start_date), MAX(end_date)
FROM    (
        SELECT  product, price, start_date, end_date,
                ROW_NUMBER() OVER (PARTITION BY product ORDER BY startDate) rn1,
                ROW_NUMBER() OVER (PARTITION BY product, price ORDER BY startDate) rn2
        FROM    mytable
        ) q
GROUP BY
        product, price, rn2 - rn1
ORDER BY
        product, MIN(start_date), price

- Quassnoi

---

它给我相同的结果。 - MayankBirthariya

{从mytable中选择产品、价格、产品、价格、MIN(start_date)、MAX(end_date)，并按照产品和价格进行分组，将会得到} - MayankBirthariya

@user1926569：好的，是我的错。请尝试更新后的查询：http://sqlfiddle.com/#!3/cf7ad/16 - Quassnoi

页面上最好的答案！ Quassnoi，您是否看到了我的这篇帖子？ 您已经知道使用减去Row_Number（）技术来查找更改组的方法有多长时间了吗？ - ErikE

@ErikE：没有，我没有看到你的帖子。https://dev59.com/jm035IYBdhLWcg3wC7YR 还有一些关于这个问题的帖子在我的博客上。 - Quassnoi

Answer 2

这里是一个SQLFiddle演示

with t2 as 
(
select t1.*,
(select count(Price) 
  from t 
  where startdate<t1.startdate 
        and Price<>t1.price
        and Product=t1.Product
)
rng  
from t as t1
)
select Product,Price,min(startDate),max(EndDate)  
from t2 group by Product,Price,RNG
order by 3

- valex

Answer 3

我的方法。

数据：

create table t ( producte varchar(50), 
                 price money, 
                 start_date date,
                 end_date date);

insert into t values
( 'apple', 4.9, '2012-01-01', '2012-01-01' ),
( 'apple', 4.9, '2012-01-02', '2012-01-02' ),
( 'apple', 8, '2012-01-04', '2012-01-04' ),
( 'cat', 5, '2012-01-01', '2012-01-01' ),
( 'cat', 6, '2012-01-02', '2012-01-02' ),
( 'cat', 6, '2012-01-03', '2012-01-03' );

查询：

with start_dates as (
  select 
    t.producte, t.price, t.start_date, t.end_date, t.start_date as gr_date    
  from 
    t left outer join 
    t t1 on 
        t.price = t1.price and                         --new
        t.producte = t1.producte and
        t.start_date = dateadd(day,1, t1.end_date )
  where t1.producte is null
  union all
  select 
      t.producte, t.price, t.start_date,t. end_date, gr_date
  from
      t inner join 
      start_dates t1 on  
        t.price = t1.price and                         --new
        t.producte = t1.producte and
        t.start_date = dateadd(day,1, t1.end_date )
)
select t.producte, t.price , min( t.start_date ), max( t.end_date )
from start_dates t
group by  t.producte, gr_date  ,t.price

Results:

| PRODUCTE | PRICE |   COLUMN_2 |   COLUMN_3 |
----------------------------------------------
|    apple |   4.9 | 2012-01-01 | 2012-01-02 |
|    apple |     8 | 2012-01-04 | 2012-01-04 |
|      cat |     5 | 2012-01-01 | 2012-01-01 |
|      cat |     6 | 2012-01-02 | 2012-01-03 |

解释

这是一个递归的CTE表达式。基本查询获取每个价格组的初始日期。递归查询查找具有此价格的最后日期。

- dani herrera

---

@user1926569，我在接受后更新了查询。请查看。 - dani herrera

请从您的答案中删除任何不正确的查询。编辑的目的不是提供历史记录，而是提供最佳答案。具有不正确的查询和答案如何随时间变化的证据并不是最佳答案。 - ErikE

@ErikE，感谢您的评论。已修复。现在正确了吗？问候。 - dani herrera

Answer 4

这里有一个建议：对于每一行，您必须找到价格不同的最大先前日期，并在该日期上进行分组。例如，对于任何2010-03-11和2010-03-16之间的行，您必须检索日期2010-03-10，因为这是价格不同（2.5与4.9）的最大先前日期。第一行（行）将返回空日期，但这不应该是问题。

然而，对于非常长的表格，这种查询可能会变得非常缓慢。因此，如果您遇到速度问题，您应该考虑添加一个列并使用游标逐步填充它：您通过日期循环遍历它，每次看到新的价格时，都会更改其值。最终的分组然后是微不足道的。

这是一些内容：

Select Product, Price, Min(StartDate) as StartDate, PreviousDate from (
    Select product, price, StartDate, (Select max (StartDate) from table_2 t3 where t3.price <> t2.price and t3.StartDate < t2.StartDate and t3.Product = t2.Product) as previousDate
    from table_2 t2) SQ

Group by Product, Price, PreviousDate
Order by PreviousDate

- SylvainL

Answer 5

我相信这是目前表现最佳的解决方案：

WITH Calc AS (
   SELECT *,
      Grp = DateAdd(day, -Row_Number()
         OVER (PARTITION BY Product, Price ORDER BY StartDate), StartDate
      )
   FROM dbo.PriceHistory
)
SELECT Product, Price, FromDate = Min(StartDate), ToDate = Max(StartDate)
FROM Calc
GROUP BY Product, Price, Grp
ORDER BY FromDate;

自己试试吧

- ErikE

---

它没有返回@op所要求的内容。 - Quassnoi

你说得对，Quassnoi。我错过了OP想要跳过间隔的要求。我会尽快回来处理这个问题。 - ErikE