将pandas组转换为sql(ite): limit in group

Question

问题

我正试图将熊猫查询转换为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")

所以我在pandas中写的查询是这样的

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的地方是非法语法

Answer 1

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

.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值最小的行。
您可以使用rowid列代替ordercolumn(在我已经提到的限制下)，或者任何其他设置行顺序的列。

你也可以做同样的FIRST_VALUE()窗口函数:

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