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

Question

我试图实现的目标很简单，但是很难解释，而且我不知道在postgres中它是否甚至有可能实现。我处于一个基本的水平。SELECT, FROM, WHERE, LEFT JOIN ON, HAVING，等等基本的东西。

我试图计算包含特定字母/数字的行数，并根据字母/数字显示该计数。

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

我要查询的表是电影名称的列表。我要做的只是对“ az”和“ 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不会在结果集中计算两次。我可以将表复制到辅助工作表中，所有数据都存放在较低的位置，并处理该查询的数据集（如果它使查询更简单或更容易构造）。

一个替代方案可能是像

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版本。

抱歉，如果我以多种方式多次说过相同的话，那么我试图获取尽可能多的信息，以便您知道我要实现的目标。

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

建议非常欢迎。

编辑1

Erwin 感谢您的修改/标记/建议。同意他们所有人。

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

kgrittn ，谢谢您的建议，但是我无法从9.0.0版本进行更新。我已经问过我的提供者，他们是否会尝试更新。

回复

感谢您的出色答复 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版本时，我有相同的结果。

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

有什么想法吗？

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

Answer 1

该查询应完成以下任务：

测试用例：

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');

询问：

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;

• 这需要PostgreSQL 9.1 ！考虑发行说明：

更改string_to_array（），以便使用NULL分隔符将字符串拆分为字符（Pavel Stehule）

以前，此操作返回一个空值。

• 您可以使用regexp_split_to_table(lower(film), '')而不是unnest(string_to_array(lower(film), NULL))（在9.1之前的版本中可用！），但通常会慢一些，并且长字符串会降低性能。

• 我generate_series()用来产生[a-z0-9]作为单独的行。并且将LEFT JOIN添加到查询中，因此每个字母都表示在结果中。

• 用于DISTINCT对每部电影计数一次。

• 永远不用担心1000行。这对于现代硬件上的现代PostgreSQL来说是花生。