SQL：最过期的数字对？

我们有此表和随机数据加载：

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问题与解答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

谢谢你的帮助。

如果我正确理解您的意思，您可以使用：

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) 
       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选择k：

36选择2 = 630