代码之家 › 专栏 › 技术社区 › Christian

在python的sqlite3中,如何计算group by不为空的行?

group-by sqlite python

Christian · 技术社区 · 6 年前

我在python 3.6中使用标准的sqlite3。我想为每一个 group_num 有多少行具有 list_num 那不是空的。空表示空字符串或空字符串。

我目前的组别是:

SELECT group_num, count(list_num)  
FROM pair_candidates WHERE list_num IS NOT NULL 
GROUP BY group_num

这计算有多少行具有相同的 群名词 但不幸的是,它没有告诉我这些行中有多少有 里斯塔姆 那不是空的。

我试着加上 WHERE list_num IS NOT NULL 在GROUPBY子句之前,这没有帮助。尝试 HAVING list_num IS NOT NULL 之后被组也没帮上忙。

我需要怎么做才能拿到我的数据?

例子:

group_num | list_num
----------+---------
1         | 
1         | 1
2         | 1
2         | 1
3         | ""
3         |

我想得到

group_num | count
----------+------
1         | 1
2         | 2
3         | 0

2 回复 | 直到 6 年前

Ilja Everilä 6 年前

选择具有 里斯塔姆 不等于空字符串。由于与空值的比较会产生未知结果,因此这些行也将被丢弃:

SELECT group_num, COUNT(*)
FROM pair_candidates
WHERE list_num != ''
GROUP BY group_num

但这不会为 群名词 没有与条件匹配的行,因此结果并不完全符合您的期望。为了产生“空”组的计数,也可以使用以下事实 COUNT(X) 返回组中x不为空的次数。

SELECT group_num, COUNT(NULLIF(list_num, ''))
FROM pair_candidates
GROUP BY group_num

NULLIF(X, Y) 如果参数不同,则返回其第一个参数;如果参数相同,则返回null,因此 COUNT(NULLIF(X, '')) 如果x是,则不计算 '' .

一个完整的例子:

In [1]: from contextlib import closing

In [2]: import sqlite3

In [3]: conn = sqlite3.connect(':memory:')

In [4]: with closing(conn.cursor()) as cur:
   ...:     cur.execute('create table pair_candidates (group_num, list_num)')
   ...:     cur.executemany('insert into pair_candidates values (?, ?)', [
   ...:         (1, None), (1, 1), (2, 1), (2, 1), (3, ""), (3, None)])
   ...:         

In [5]: with closing(conn.cursor()) as cur:
   ...:     cur.execute('''select group_num, count(nullif(list_num, ''))
   ...:                    from pair_candidates
   ...:                    group by group_num''')
   ...:     res = cur.fetchall()
   ...:     

In [6]: res
Out[6]: [(1, 1), (2, 2), (3, 0)]

Serge Ballesta 6 年前

不过,不能简单地从sql查询中获取它。空是一种特殊的动物,只能被 IS NULL 绝对不一样 "" . 但这并不是全部: SELECT COUNT GROUP BY 永远不会返回计数为0的行,而只是不返回该组的行。毕竟,查询没有返回该组的行,count只是一个聚合 在返回的行上 .

当然可以使用外部连接在结果集中强制执行一行,但除非您真正精通sql,否则肯定会相当复杂。但是混合来自python代码的2个查询是很简单的:第一个返回组列表并将计数初始化为0,第二个返回非零计数并可用于更新初始映射。

假设 con 是到sqlite3数据库的活动连接,可以执行以下操作:

groups = { x[0]: 0 for x in con.execute(
    "SELECT distinct group_num FROM pair_candidates").fetchall() }
groups.update({ x[0]: x[1] for x in con.execute(
    """SELECT group_num, count(list_num)
    FROM pair_candidates WHERE list_num != ""
    GROUP BY group_num""").fetchall() })
print(groups)

这将按预期返回:

{1: 1, 2: 2, 3: 0}