将pandas组转换为sql（ite）：组中的限制

debugcn 发表于 Dev

雷公藤

问题

我正在尝试将熊猫查询转换为SQL（ite）查询。我翻译成SQL的主要问题是试图在组中设置限制。

这是示例数据库的设置（使用python）。

import sqlite3
from random import choices

import pandas as pd

df = pd.DataFrame(
    dict(
        montecarlo=choices(range(1, 501), k=10_000),
        time=choices(range(1, 1_000), k=10_000),
        tracking_id=choices([1, 2, 3], k=10_000),
        passed=choices([True, False], k=10_000),
    )
)

df.sort_values(by=["montecarlo", "time"], inplace=True)

conn = sqlite3.connect("test.db")
df.to_sql("mytable", conn, index=False, if_exists="replace")

所以我要用熊猫写的查询是这样的

import numpy as np

def first_time_if_exists(df: pd.DataFrame) -> float:
    try:
        return df.loc[df.passed.eq(1)].time.iloc[0]
    except IndexError:
        # Sometimes there is no pass for a given df
        return np.NaN


result = (
    df.groupby(["montecarlo", "tracking_id"])  # Group by montecarlo and id
    .apply(first_time_if_exists)  # Get time of first pass for each group
    .rename("first_time")  # Give the pandas series a name
    .reset_index()  # Pop montecarlo and tracking_id back into normal columns
)

这里result看起来像这样

      montecarlo  tracking_id  first_time
0              1            1        51.0
1              1            2       289.0
2              1            3        14.0
3              2            1       201.0
4              2            2       121.0
...          ...          ...         ...
1492         499            2       143.0
1493         499            3       129.0
1494         500            1        25.0
1495         500            2       147.0
1496         500            3       251.0

[1497 rows x 3 columns]

到目前为止我尝试过的

我认为我可以通过SQL查询获得大部分信息。

SELECT 
    montecarlo, tracking_id, time
FROM 
    mytable
WHERE 
    passed = 1
GROUP BY 
    montecarlo, tracking_id;

但是，这给了我每组所有的时间，而不仅仅是第一组。我也尝试过

SELECT 
    montecarlo, tracking_id, time
FROM 
    mytable
WHERE 
    passed = 1
LIMIT
    1
GROUP BY 
    montecarlo, tracking_id;

但是我放置的位置LIMIT是非法语法。

福帕斯

在任何关系数据库中，所有表都是无序集合，因此没有第一行或最后一行。
所以这部分代码：

.apply(first_time_if_exists)  # Get time of first pass for each group

在SQL中是没有意义的，除非有一列表示行的顺序，例如自动递增id或带有插入行时间戳的datetime列。

SQLite为所有表提供了rowid您可以用于此目的的列，但是并不总是保证其最大值将是最后插入的行，因为删除行后，rowid可能会重复使用s的缺失值。

如果可以使用这样的列，请说ordercolumn，那么您可以使用以下方法进行操作：

SELECT montecarlo, tracking_id, time
FROM mytable
WHERE passed = 1
GROUP BY montecarlo, tracking_id
HAVING MIN(ordercolumn)

该查询尽管在其他数据库中无效，但在SQLite中有效，并针对montecarlo和tracking_id的每个组合（其中的值ordercolumn最小）返回。
您可以使用而不是ordercolumn列rowid（在我已经提到的限制下）或设置行顺序的任何其他列。

您也可以使用FIRST_VALUE()window函数执行相同的操作：

SELECT DISTINCT montecarlo, tracking_id, 
       FIRST_VALUE(time) OVER (PARTITION BY montecarlo, tracking_id ORDER BY ordercolumn) AS time
FROM mytable
WHERE passed = 1

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。