时间戳

首页 > 解决方案 > 有没有类似于 Pandas tail() 的 SQLite 函数

sql - 有没有类似于 Pandas tail() 的 SQLite 函数

问题描述

我正在努力解决在 Pandas 中很容易的 SQLite 问题。我有大量数据正在增长，并且已经达到以下对 pandas 数据帧的调用导致内存错误（内存不足）的程度。

    df_tail = df.groupby(['Phase', 'Cycle']).tail(time_for_avg)

我认为使用 SQLite 形式的数据库为我做这个处理可能是明智的；但是，我被困在如何像处理 Pandas 一样抓取一部分数据。我基本上想从一个更大（约 10 小时）的集合中获取最后 120 条（2 分钟）的数据，该集合由 pandas 中的 groupby 调用定义。我已经设法像这样在 SQL 中获取 groupby 查询

    SELECT 
        Phase, Cycle, 
        AVG("data1"), 
        AVG("data2")
    FROM 
        table
    GROUP BY
        Phase, Cycle

但是我的 SQL 能力相当低，这就是我卡住的地方。我想不出一种方法来获取 groupby 仅对数据的最后一部分计算的平均值，因为我的 SQL 代码计算了 groupby 返回的整个数据集的平均值。

数据的描述是：有四个阶段 - 阶段 1 到阶段 4，并且这些在数据集中重复了很多次。周期编号从 0 开始以 1 递增，因此 0、1、2.... 所以周期 1 将与阶段 1 相关联，周期 2 将与阶段 2 相关联...周期 5 将再次与阶段 1 相关联随着阶段的重复等等。我希望将每个阶段和周期的平均值存储在另一个表中以获取结果。

欢迎任何帮助或指示。谢谢你。

编辑 2020 年 7 月 16 日

抱歉，如果有一些混乱，这是写在“绝望地举起手”的时刻......

对数据的更好描述可能是：

Cycle  |  Phase
1      |  phase 1
2      |  phase 2
3      |  phase 3
4      |  phase 4
5      |  phase 1
6      |  phase 2
7...

上表中的每一行大约有 36000 行（每秒记录 10 小时的数据）。记录的每个时间戳也有 60 个数据点。

Raw data
Row #  |  Cycle  |  Phase    |  data 1  |  data 2 ... data 60
1      |  1      |  phase 1  |  0.1     |  0.11
2      |  1      |  phase 1  |  0.11    |  0.12
...    |  ...    |  ...      |  ...     |  ...
36000  |  1      |  phase 1  |  0.14    |  0.16
36001  |  2      |  phase 2  |  0.11    |  0.20
...    |  ...    |  ...      |  ...     |  ...
72000  |  2      |  phase 2  |  0.14    |  0.16
72001  |  3      |  phase 3  |  0.11    |  0.20
...    |  ...    |  ...      |  ...     |  ...
108000 |  3      |  phase 3  |  0.16    |  0.20
108001 |  4      |  phase 4  |  0.15    |  0.20
...    |  ...    |  ...      |  ...     |  ...
144000 |  4      |  phase 4  |  0.11    |  0.22
144001 |  5      |  phase 1  |  0.11    |  0.22
...    |  ...    |  ...      |  ...     |  ...

我有大约 20 GB 的数据需要处理。所以我有数百个周期的数据，我需要对每个周期的最后 120 行进行平均并存储在一个新表中。因此，在上表中，我需要拉出第 35880 到 36000 行，并将每个数据列平均并作为一行放在另一个表中。

Averages
|  Cycle  |  Phase    |  Avg(data 1)  |  Avg(data 2) ... Avg(data 60)
|  1      |  phase 1  |  0.11         |  0.12
|  2      |  phase 2  |  0.11         |  0.12
|  3      |  phase 3  |  0.11         |  0.12
|  4      |  phase 4  |  0.11         |  0.12
|  5      |  phase 1  |  0.11         |  0.12
...

编辑 2020 年 7 月 17 日

添加创建表语句。有一个自动递增的主键列，类似于上面的“行#”列。

CREATE TABLE Raw_Data (
'Raw_data_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
'Date' TEXT,
'Time' TEXT,
'Phase' TEXT,
'Cycle' INTEGER,
'data 1' REAL,
'data 2' REAL,
...
'data 60' REAL
);

标签： sqlpandassqlitegroup-bytail

解决方案

如果您只想要最后一个“组”的数据，那么您不想要GROUP BY- 我认为这会起作用：

第 1 步：找到最新的Phase+Cycle元组：

SELECT
    Phase,
    Cycle
FROM
    table
ORDER BY
    Phase,
    Cycle
LIMIT
    1

table第 2 步：使用该结果过滤整个集合：

SELECT
    *
FROM
    table
    INNER JOIN
    (
        SELECT
            Phase,
            Cycle
        FROM
            table
        ORDER BY
            Phase,
            Cycle
        LIMIT
            1
    ) AS q ON table.Phase = q.Phase AND table.Cycle = q.Cycle

第三步：聚合处理：

如果它很复杂，您可以在应用程序代码中进行聚合和处理 - 或者通过将 SQLite 内置聚合直接添加到查询中：

SELECT
    Phase,
    Cycle,
    AVG( data1 ) AS avg1,
    AVG( data2 ) AS avg2,
    SUM( data3 ) AS sum3,
    -- etc
FROM
    table
    INNER JOIN
    (
        SELECT
            Phase,
            Cycle
        FROM
            table
        ORDER BY
            Phase,
            Cycle
        LIMIT
            1
    ) AS q ON table.Phase = q.Phase AND table.Cycle = q.Cycle

推荐阅读