将某些记录滑动到同一日期的运行结束 [英] Sliding certain records to the end of a run of the same date

问题描述

我有三列-

• TheDate-显然是日期.
• TheID-严格增加的ID.
• TheType-一种记录类型.

• TheDate - Obviously a date.
• TheID - A strictly increasing ID.
• TheType - A Record type.

我希望它们在几乎所有情况下都按TheID排序，除了一种记录类型.特殊记录类型的记录必须出现在所有具有相同日期的记录的末尾.

I want these to sort by TheID in almost all cases except for just one record type. Records of the special record type must appear at the end of all records with the same date.

示例:

我希望记录类型101在所有具有相同日期的其他记录之后显示在之后.在所有其他情况下，TheID控制顺序.

I want the record type 101 to appear after all other records that have the same date. In all other cases TheID controls the order.

我的尝试如下:

ORDER BY 
TheDate, 
CASE WHEN TheType = 101 THEN 1 ELSE 0 END, 
TheID

几乎 可以满足我的要求，但效果却要好得多-即它将由TheDate重新排序，而这并不是我想要的.

which nearly does what I want but is doing far more - i.e. it will reorder by TheDate which is not waht I want.

如果以后不在数据中出现相同的日期，我不在乎-只是当包含类型101(按TheID排序)的记录序列都具有相同的日期时，我希望输入101最后.

If the same date occurs later in the data I don't care - it's just when a sequence of records containing a type 101 (when sorted by TheID) all have the same date I want type 101 to be last.

推荐答案

这很复杂.首先，您必须找到连续的日期记录，所以使用

This is complicated. First you must find consecutive date records, so with

thedate     theid  thetype
2014-07-12   5001       59
2014-07-12   5002      101
2014-07-12   5003       88
2014-07-13   5004       10
2014-07-12   5005       60

您会将2014-07-12识别为前三个记录的一个事件，最后一个记录的另一个事件.第二条记录必须在结果中排​​在第3位，而不是第5位.

you would identify 2014-07-12 as one occurrence for the first three records and another for the last record. The second record would have to get position #3 in your results, not #5.

您可以通过以下方式实现此目的:通过使用第一个LAG来查找连续记录，从而为连续记录提供组密钥，从而在组更改上创建一个标志，然后累加这些标志.

You achieve this by giving consecutive records a group key by using first LAG to look into the previous record, thus creating a flag on group change, and then cumulating these flags.

select thedate, theid, thetype
from
(
  select 
    thedate, theid, thetype,
    sum(new_group) over (order by theid) as group_key
  from
  (
    select
      thedate, theid, thetype,
      case when lag(thedate) over (order by theid) = thedate then 0 else 1 as new_group
    from mytable
  ) marked
) grouped
order by 
  group_key,
  case when thetype = 101 then 1 else 0 end,
  theid;

这篇关于将某些记录滑动到同一日期的运行结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！