如何在没有违规的情况下进行GROUP BY？

问题描述：

============================= 
| Timestamp | Count | 
============================= 
| 12/30/2016 | 322  | 
| 12/29/2016 | 322  | 
| 12/28/2016 | 322  | 
| 12/4/2016 | 541  | 
| 12/3/2016 | 541  | 
| 12/2/2016 | 541  | 
| 12/1/2016 | 322  | 
| 11/30/2016 | 322  | 
| 11/29/2016 | 322  | 
=============================

我想SELECT MAX([Timestamp]), Count FROM [dbo].[Table] GROUP BY [Count]没有打破[Timestamp]顺序，即输出应该是：

============================= 
| Timestamp | Count | 
============================= 
| 12/30/2016 | 322  | 
| 12/4/2016 | 541  | 
| 12/1/2016 | 322  | 
=============================

所以12/1/2016 | 322不被隐藏GROUP BY。有人知道如何编写这样的查询吗？我需要一个EntityFramework Linq查询，知道一个T-SQL模拟器会很棒。

group by count和geting max不起作用？！？ – Damirchi

@Damirchi否，因为它会隐藏第二个'322'的值 – dyatchenko

你说的“每个计数的最大时间戳”，并且最多有一个322不是更多 – Damirchi

答

我认为你需要这样的查询：

select [Timestamp], [Count] 
from (
    select * 
     --//Step 3: Now just find the max value 
     , row_number() over (partition by seqGroup order by [Timestamp] desc) as seq 
    from (
     select * 
      --//Step 2: you need to generate a group number for each sequence 
      , sum(changed) over (order by [Timestamp] desc) as seqGroup 
     from (
      select * 
       --//Step 1: you need to track changes over [Count] 
       , case when coalesce(lag([Count]) over (order by [Timestamp] desc), [Count]) = [Count] then 0 else 1 end as changed 
      from yourTable) t1 
     ) t2 
    ) t3 
where seq = 1;

看起来这个查询完美无缺！非常感谢。你知道一个Linq模拟器吗？ – dyatchenko

我并不擅长LinQ，但现在你可以为这个查询请求一个LinQ;）。 –

答

我看到前面的答案，而他的依据书面LINQ要求上。

1）创建你initTable：在initTable

var initTable = new List<Tuple<DateTime, int>>(); 
       initTable.Add(Tuple.Create(new DateTime(2016, 12, 30), 322)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 12, 29), 322)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 12, 28), 322)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 12, 4), 541)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 12, 3), 541)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 12, 2), 541)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 12, 1), 322)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 11, 30), 322)); 
       initTable.Add(Tuple.Create(new DateTime(2016, 11, 29), 322));

2）添加行索引，以便通过时间戳：

//get row index 
var table = initTable 
      .Where(x => true) 
      .Select((x, i) => new 
      { 
       Timestamp = x.Item1.ToString("MM-dd-yyyy"), 
       Count = x.Item2, 
       //row index 
       Index = i 
      }) 
      .OrderByDescending(x => x.Timestamp);

3）查询

var query = table 
      //#1 get prev row count 
      .Select(x => new 
      { 
       x.Timestamp, 
       x.Count, 
       x.Index, 
       //previous row count (-1 for first row, not default null) 
       Prev = table.LastOrDefault(y => y.Index < x.Index) == null ? 
         -1 : 
         table.LastOrDefault(y => y.Index < x.Index).Count 
      }) 
      //#2 set group flag 
      .Select(x => new 
      { 
       x.Timestamp, 
       x.Count, 
       x.Index, 
       x.Prev, 
       GroupId = x.Count == x.Prev 
      }) 
      //#3 
      .Where(x => x.GroupId == false) 
      .Select(x => new 
      { 
       x.Timestamp, 
       x.Count 
      });

您可以将其插入LinqPad并验证结果。

我希望这会有帮助。

如何在没有违规的情况下进行GROUP BY？

相关推荐