如何从sqlite文件中选择连续数据

Question

我在SQLite文件中有一些数据，并希望从C#中操作它。

数据包括三列：Time、Temperature和Power。我要找出Temperature大于30，持续时间超过3分钟的记录组，并计算每组的平均功率(连续时间范围)。

从表中选择连续范围

如何在sql中选择连续日期

我在上面找到了两个答案，但达不到我的答案。

它可以直接使用string (对于SQLiteDataAdapter)获得结果。或选择所有数据并使用c# (datatable.Select、Computer或LINQ)对其进行操作。

| No   | Time              | Temp | Power |
| ---- | ----------------- | ---- | ----- |
| 1    | 2019-11-4 0:00:00 | 25   | 1200  |
| 2    | 2019-11-4 0:01:10 | 30   | 1000  |
| 3    | 2019-11-4 0:02:20 | 31   | 680   |
| 4    | 2019-11-4 0:03:30 | 34   | 960   |
| 5    | 2019-11-4 0:04:40 | 29   | 800   |
| 6    | 2019-11-4 0:05:50 | 31   | 600   |
| 7    | 2019-11-4 0:07:00 | 32   | 400   |
| 8    | 2019-11-4 0:08:10 | 33   | 900   |
| 9    | 2019-11-4 0:09:20 | 34   | 1000  |
| 10   | 2019-11-4 0:10:30 | 39   | 200   |
| 11   | 2019-11-4 0:11:40 | 24   | 350   |

预期结果:选择Temp≥30、连续时间>3 mean的连续记录为一组，计算Power均值。

示例表中包含从6到10的数字的一组，平均值为620。

感谢在我的作品中使用的all.The代码：

        string sql = "WITH CTE AS (" +
                     "SELECT(Temp > 30) AS tempgt30,Time, Power," +
                            "ROW_NUMBER() OVER(ORDER BY No) rn," +
                            "ROW_NUMBER() OVER(PARTITION BY(Temp > 30) ORDER BY No) rn2" +
                     " FROM data" +
                     ")" +
                    " SELECT MIN(rn)AS minrow,MAX(rn) as maxrow," +
                            "strftime('%s', MAX(Time)) - strftime('%s', MIN(Time)) AS duration," +
                            "AVG(Power) AS mean" +
                    " FROM CTE" +
                    " WHERE tempgt30" +
                    " GROUP BY(rn2 - rn)" +
                    " HAVING duration > 180;";

        con.Open();

        if (con.State == ConnectionState.Open)
        {
            using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(sql, con))
            {
                dataAdapter.Fill(datatable);
                con.Close();
            }
        }

Answer 1

你看的是正确的问题，因为这确实是一个空白和岛屿问题。在你的例子中，“岛屿”是Temp > 30的读数。我们可以使用以下查询来形成它们并计算岛屿的平均Power和持续时间(以秒为单位)：

WITH CTE AS (
SELECT (Temp > 30) AS tempgt30, 
       Time,
       Power,
       ROW_NUMBER() OVER (ORDER BY No) rn,
       ROW_NUMBER() OVER (PARTITION BY (Temp > 30) ORDER BY No) rn2
FROM data
)
SELECT MIN(rn) AS minrow, 
       MAX(rn) as maxrow, 
       MIN(Time) AS start,
       MAX(Time) AS end,
       strftime('%s', MAX(Time)) - strftime('%s', MIN(Time)) AS duration,
       AVG(Power) AS mean
FROM CTE
WHERE tempgt30
GROUP BY (rn2 - rn)
HAVING duration > 180

输出：

minrow  maxrow  start                   end                     duration    mean
6       10      2019-11-04 00:05:50     2019-11-04 00:10:30     280         620

dbfiddle演示