计算包含字母/数字的行数

我想要实现的目标很简单，但是解释起来有点困难，我不知道在 postgres 中这是否真的可能。我处于相当基础的水平。SELECT, FROM, WHERE, LEFT JOIN ON, HAVING，等等基本的东西。

我正在尝试计算包含特定字母/数字的行数，并显示该字母/数字的计数。

即有多少行包含包含“a/A”的条目（不区分大小写）

我正在查询的表是电影名称列表。我想做的就是对“a-z”和“0-9”进行分组和计数并输出总数。我可以按顺序运行 36 个查询：

SELECT filmname FROM films WHERE filmname ilike '%a%'
SELECT filmname FROM films WHERE filmname ilike '%b%'
SELECT filmname FROM films WHERE filmname ilike '%c%'

然后对结果运行 pg_num_rows 以查找我需要的数字，依此类推。

我知道喜欢有多强烈，而且喜欢得更多，所以我宁愿避免这种情况。虽然数据（如下）的数据有大小写，但我希望结果集不区分大小写。即“盯着山羊的男人”，a/A、t/T 和 s/S 不会对结果集计数两次。我可以将该表复制到辅助工作表，其中数据全部为 strtolower，并处理该查询的数据集（如果它使查询更简单或更容易构建）。

另一种选择可能是这样的

SELECT sum(length(regexp_replace(filmname, '[^X|^x]', '', 'g'))) FROM films;

对于每个字母组合，但同样是 36 个查询、36 个数据集，我更希望能够在单个查询中获取数据。

这是我的数据集中的 14 部电影的简短数据集（实际上包含 275 行）

District 9
Surrogates
The Invention Of Lying
Pandorum
UP
The Soloist
Cloudy With A Chance Of Meatballs
The Imaginarium of Doctor Parnassus
Cirque du Freak: The Vampires Assistant
Zombieland
9
The Men Who Stare At Goats
A Christmas Carol
Paranormal Activity

如果我手动将每个字母和数字放在一列中，然后通过在该列中给它一个 x 来注册该字母是否出现在电影标题中，然后将它们计数以产生总数，我将得到如下所示的内容。 x 的每个垂直列都是该电影名称中字母的列表，无论该字母出现多少次或其大小写。

上面短集的结果是：

A  x x  xxxx xxx  9 
B       x  x      2 
C x     xxx   xx  6
D x  x  xxxx      6
E  xx  xxxxx x    8
F   x   xxx       4 
G  xx    x   x    4
H   x  xxxx  xx   7
I x x  xxxxx  xx  9
J                 0
K         x       0
L   x  xx  x  xx  6
M    x  xxxx xxx  8
N   xx  xxxx x x  8
O  xxx xxx x xxx  10
P    xx  xx    x  5
Q         x       1
R xx x   xx  xxx  7
S xx   xxxx  xx   8
T xxx  xxxx  xxx  10
U  x xx xxx       6
V   x     x    x  3
W       x    x    2
X                 0 
Y   x   x      x  3
Z          x      1 
0                 0  
1                 0  
2                 0 
3                 0
4                 0
5                 0
6                 0
7                 0
8                 0
9 x         x     1

在上面的示例中，每一列都是一个“电影名称”，如您所见，第 5 列仅标记“u”和“p”，第 11 列仅标记“9”。最后一列是每个字母的计数。

我想以某种方式构建一个查询，给出结果行：A 9、B 2、C 6、D 6、E 8 等，同时考虑到从我的电影列中提取的每个行条目。如果该字母没有出现在任何行中，我想要一个零。

我不知道这是否可能，也不知道在 php 中系统地执行 36 个查询是否是唯一的可能性。

当前数据集中有 275 个条目，每月增长约 8.33 个（每年 100 个）。我预计到 2019 年它将达到大约 1000 行，届时我无疑将使用完全不同的系统，因此我不需要担心使用庞大的数据集来进行搜索。

目前最长的标题是“波西·杰克逊与奥林匹亚众神：神火之盗”，有 50 个字符（是的，我知道这部电影很糟糕；-），最短的是 1，“9”。

我正在运行 Postgres 9.0.0 版本。

抱歉，如果我以多种方式多次说过同一件事，我会尽力获取尽可能多的信息，以便您知道我想要实现的目标。

如果您需要任何说明或更大的数据集进行测试，请询问，我将根据需要进行编辑。

非常欢迎提出建议。

Edit 1

Erwin感谢您的编辑/标签/建议。同意他们所有人的观点。

按照建议修复了丢失的“9”拼写错误Erwin。我的手动转录错误。

kgrittn，感谢您的建议，但我无法从 9.0.0 更新版本。我已询问我的提供商是否会尝试更新。

Response

感谢您的精彩回复Erwin

对于回复延迟深表歉意，但我一直在努力让您的查询发挥作用，并学习新的关键字来理解您创建的查询。

我调整了查询​​以适应我的表结构，但结果集不符合预期（全为零），因此我直接复制了您的行并得到了相同的结果。

虽然两种情况下的结果集都列出了所有 36 行以及相应的字母/数字，但所有行的计数 (ct) 均显示为零。

我尝试解构查询以查看它可能在哪里失败。

的结果

SELECT DISTINCT id, unnest(string_to_array(lower(film), NULL)) AS letter
FROM  films

是“未找到行”。也许当从更广泛的查询中提取时应该如此，我不确定。

当我删除 unnest 函数时，结果是 14 行全部带有“NULL”

如果我调整功能

COALESCE(y.ct, 0) to COALESCE(y.ct, 4)<br />

然后我的数据集对每个字母都用 4 进行响应，而不是像前面所解释的那样为零。

简要阅读了 COALESCE 上的“4”作为替代值后，我猜测 y.ct 为 NULL 并被第二个值替代（这是为了覆盖序列中字母不匹配的行，即如果没有电影包含“q”，那么“q”列将具有零值而不是 NULL？）

我尝试使用的数据库是 SQL_ASCII，我想知道这是否是一个问题，但我在使用 UTF-8 运行 8.4.0 版本时得到了相同的结果。

如果我犯了一个明显的错误，但我无法返回我需要的数据集，我深表歉意。

有什么想法吗？

再次感谢您的详细回复和解释。

这个查询应该完成这个工作：

测试用例：

CREATE TEMP TABLE films (id serial, film text);
INSERT INTO films (film) VALUES
 ('District 9')
,('Surrogates')
,('The Invention Of Lying')
,('Pandorum')
,('UP')
,('The Soloist')
,('Cloudy With A Chance Of Meatballs')
,('The Imaginarium of Doctor Parnassus')
,('Cirque du Freak: The Vampires Assistant')
,('Zombieland')
,('9')
,('The Men Who Stare At Goats')
,('A Christmas Carol')
,('Paranormal Activity');

Query:

SELECT l.letter, COALESCE(y.ct, 0) AS ct
FROM  (
    SELECT chr(generate_series(97, 122)) AS letter  -- a-z in UTF8!
    UNION ALL
    SELECT generate_series(0, 9)::text              -- 0-9
    ) l
LEFT JOIN (
    SELECT letter, count(id) AS ct
    FROM  (
        SELECT DISTINCT  -- count film once per letter
               id, unnest(string_to_array(lower(film), NULL)) AS letter
        FROM   films
        ) x
    GROUP  BY 1
    ) y  USING (letter)
ORDER  BY 1;

• 这需要 PostgreSQL9.1！考虑发行说明 http://www.postgresql.org/docs/current/interactive/release-9-1.html:

更改 string_to_array() 以便 NULL 分隔符将字符串拆分为 角色（帕维尔·斯特胡勒）

以前这会返回空值。

• 您可以使用regexp_split_to_table(lower(film), '') http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-OTHER， 代替unnest(string_to_array(lower(film), NULL)) http://www.postgresql.org/docs/current/interactive/functions-array.html#ARRAY-FUNCTIONS-TABLE（适用于 9.1 之前的版本！），但它通常会慢一些，并且对于长字符串，性能会下降。

• I use generate_series()生产[a-z0-9]作为单独的行。并对查询进行 LEFT JOIN，因此每个字母都会在结果中表示。

• Use DISTINCT每部电影都数一次。

• 永远不用担心 1000 行。这对于现代硬件上的现代 PostgreSQL 来说简直就是小菜一碟。