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

Question

我所要达到的目标是直截了当的，但这有点难以解释，我不知道这是否真的可能在博士后。我处于一个相当基本的水平。SELECT, FROM, WHERE, LEFT JOIN ON, HAVING，e.t.c基本的东西。
我试图计算包含特定字母/数字的行数，并显示与该字母/数字相对应的行数。
即有多少行的条目包含“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_行以找到我需要的数字，依此类推。
我知道爱是多么强烈，我更喜欢这样，所以我宁愿避免。尽管数据（下面）有大小写，但我希望结果集不区分大小写。也就是说“盯着山羊看的人”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

在上面的例子中，如你所见，每一列都是一个“filmname”，第5列只标记一个“u”和一个“p”，第11列只标记一个“9”。最后一栏是每个字母的计数。
我想以某种方式构建一个查询，给出结果行：a 9、B 2、C 6、d6、E 8 E.t.C，其中考虑了从films列提取的每一行条目。如果那封信没有出现在任何一行，我想要一个零。
我不知道这是否可能，也不知道是否可以在php中系统地使用36个查询。
在当前的数据集中，有275个条目，每月增长约8.33条（每年100条）。我预计到2019年它将达到1000行左右，到那时我无疑将使用一个完全不同的系统，所以我不需要担心使用一个庞大的数据集来搜索。
目前最长的片名是《珀西·杰克逊与奥林匹亚人：闪电窃贼》（Percy Jackson&The Olympians:The Lightning小偷），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

是“找不到行”。也许当从更广泛的问题中提取出来的时候，我不确定。
当我删除unest函数时，结果是14行，全部为“NULL”
如果我调整功能

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

然后我的数据集对每个字母的响应都是4，而不是前面解释的0。
在合并时简要阅读了“4”作为替换值，我猜测y.ct为空，并用第二个值替换（这是为了覆盖序列中字母不匹配的行，即如果没有电影包含“q”，那么“q”列将有一个零值而不是空值？）
我尝试使用的数据库是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！考虑一下 release notes：
将string_更改为_array（），以便使用空分隔符将字符串拆分为
人物（Pavel Stehule）
在此之前，它返回了一个空值。
您可以使用 regexp_split_to_table(lower(film), '')，而不是 unnest(string_to_array(lower(film), NULL))（适用于9.1之前的版本！），但通常会慢一点，并且长字符串会降低性能。
我使用 generate_series()将 [a-z0-9]作为单独的行生成。左键连接到查询，因此结果中的每个字母都有表示。
使用 DISTINCT对每张胶片计数一次。
千万别担心1000排。这是现代PostgreSQL在现代硬件上的微不足道的成就。