主页 / 问题 / 1361239
Accepted
Radzhab
Asked:2022-05-15 16:21:35 +0000 UTC2022-05-15 16:21:35 +0000 UTC

如何从数据中获取第一个和最后一个值？

  • 772

这里有张桌子。我想按索引分割这个范围，并从中获取第一个值和最后一个值。

datetime,index

1633378385000,4
1633378451000,4
1633378505000,4
1633378582000,4
1633378652000,4
1633378717000,4
1633378782000,""
1633378848000,""
1633378913000,""
1633383874000,""
1633383940000,5
1633384007000,5
1633384011000,5
1633384205000,5
1633384266000,5
1633384340000,5
1633387730000,""
1633387750000,""
1633388380000,4
1633389103000,4
1633389181000,4
1633389247000,4

输出应该是

1633378385000,4
1633378717000,4
1633383940000,5
1633384340000,5
1633388380000,4
1633389247000,4

我想用first_value-来做last_value，但它会查看整个表格，但我需要它一块一块地。

在此处输入图像描述

sql

1 个回答

  1. Best Answer

    MySQL 8+ 版本的解决方案：

    WITH 
cte1 AS ( SELECT *, `index` <> LAG(`index`) OVER (ORDER BY `datetime`) newgroup
          FROM test ),
cte2 AS ( SELECT *, COALESCE(SUM(newgroup) OVER (ORDER BY `datetime`), 0) groupnum
          FROM cte1 )
SELECT MIN(`datetime`) datetime_start,
       MAX(`datetime`) datetime_end,
       ANY_VALUE(`index`) `index`
FROM cte2
WHERE `index`
GROUP BY groupnum
ORDER BY 1;

    或者

    
WITH 
cte AS ( SELECT *,
                COALESCE(LAG(`index`) OVER (ORDER BY `datetime`), '') previousindex,
                COALESCE(LEAD(`index`) OVER (ORDER BY `datetime`), '') nextindex 
         FROM test )
SELECT `datetime`, `index`
FROM cte
WHERE '' IN (previousindex, nextindex)
 AND `index` <> '' 
ORDER BY 1;

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e6104479de1664121d560f2629c3a3b5

    PS。该任务是标准的，称为“间隙和岛屿”。

    • 1

相关问题