SQL：最长逾期的数字对？

Question

我们有一个表格和随机数据加载：

CREATE TABLE [dbo].[webscrape](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [date] [date] NULL,
    [value1] [int] NULL,
    [value2] [int] NULL,
    [value3] [int] NULL,
    [value4] [int] NULL,
    [value5] [int] NULL,
    [sumnumbers] AS ([value1]+[value2]+[value3]+[value4]+[value5])
) ON [PRIMARY]


declare @date date = '1990-01-01',
@endDate date = Getdate()

while @date<=@enddate
begin
insert into [dbo].[webscrape](date,value1,value2,value3,value4,value5)
SELECT @date date,FLOOR(RAND()*(36-1)+1) value1,
FLOOR(RAND()*(36-1)+1) value2,
FLOOR(RAND()*(36-1)+1) value3,
FLOOR(RAND()*(36-1)+1) value4,
FLOOR(RAND()*(36-1)+1) value5

set @date = DATEADD(day,1,@date)
end

select * from [dbo].[webscrape] 

在SQL中，我们如何返回一对值，在给定日期之前已经最长时间没有出现过？

而且（如果您知道的话）在Power BI Q&A NLP中，我们如何进行映射，以便用自然语言提问“最长时间没有出现的一对数是什么时候发生的？”

过期是指自给定日期以来时间间隔最长的一对数字。

更新：我正在尝试这个非常丑陋的代码。有什么想法：

  select *
    from (
      select date,value1 number1,value2 number2 from webscrape union all  
      select date,value1,value3 from webscrape union all
      select date,value1,value4 from webscrape union all
      select date,value1,value5 from webscrape union all
      select date,value2,value3 from webscrape union all
      select date,value2,value4 from webscrape union all
      select date,value2,value5 from webscrape union all
      select date,value3,value4 from webscrape union all
      select date,value3,value5 from webscrape union all
      select date,value4,value5 from webscrape 

    ) t order by date


    ----------------------------------

    select t.number1,t.number2, count(*)
     as counter
    from (
      select value1 number1,value2 number2 from webscrape union all  
      select value1,value3 from webscrape union all
      select value1,value4  from webscrape union all
      select value1,value5 from webscrape union all
      select value2,value3 from webscrape union all
      select value2,value4  from webscrape union all
      select value2,value5 from webscrape union all
      select value3,value4  from webscrape union all
      select value3,value5 from webscrape union all
      select value4,value5 from webscrape 
    ) t

group by t.number1,number2
order by counter

感谢任何帮助。

- Hell.Bent

---

我不太确定你的意思。很好，你已经发布了DDL+DML的示例数据，但如果你能用确定性的示例数据和期望的结果[编辑]你的问题，那么就可以更好地理解你想要实现什么。此外，你应该包括你已经尝试过什么。 - Zohar Peled

在随机数和/或彩票中，不存在所谓的热门数字或过期数字。如果样本是真正随机的，每次抽奖都与之前的抽奖结果无关。 - John Cappelletti

热和冷只是一个术语，它在轮盘和其他游戏中被使用。它仅代表了宇宙中的频率。 - Hell.Bent

Answer 1

如果我理解正确，您可以使用以下方法：

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY c1, c2 ORDER BY date DESC) AS rn
  FROM webscrape
  CROSS APPLY (
       SELECT c1 = IIF(c1 < c2, c1, c2), c2 = IIF(c1 > c2, c1, c2) 
       FROM (VALUES (value1, value2),
                    (value1, value3),
                    (value1, value4),
                    (value1, value5),
                    (value2, value3),
                    (value2, value4),
                    (value2, value5),
                    (value3, value4),
                    (value3, value5),
                    (value4, value5)) s(c1, c2)
       ) sub
) 
SELECT * 
FROM cte
WHERE rn = 1
ORDER BY date;

db<>fiddle演示

工作原理：

1）CROSS APPLY 将值展开成多行并按 c1,c2 排序

2）ROW_NUMBER 按日期倒序分区排列 c1、c2

3）获取每个组的第一个出现并按日期排序

---

快速检查：组合数量为 n choose k:

36 选 2 = 630

- Lukasz Szozda