sql - 同一查询中的多个通配符计数

Question

我的工作职责之一是负责挖掘和营销大型时事通讯订阅数据库。我的每一份时事通讯都有四个栏目(newsletter_status、newsletter_datejoined、newsletter_dateunsub 和 newsletter_unsubmid)。

除了这些专栏，我还有一个master unsub专栏，我们的客户服务部门。可以更新以适应希望从我们所有邮件中删除的愤怒订阅者，如果发生硬退回(或一定数量的软退回)，另一个列会更新，称为 emailaddress_status。

当我为一个列表提取当前有效订阅者的计数时，我使用以下语法:

select count (*) from subscriber_db
WHERE (emailaddress_status = 'VALID' OR emailaddress_status IS NULL)
AND newsletter_status = 'Y'
and unsub = 'N' and newsletter_datejoined >= '2013-01-01';

我想要的是一个查询，它查找具有 %_status 的所有列，上述条件按当前计数大小排序。

我希望它看起来像这样:

等等

我在网上搜索了几个月寻找类似的东西，但除了在终端中运行它们并导出结果外，我无法在一次查询中成功获得所有结果。

我正在运行 PostgreSQL 9.2.3。

一个合适的测试用例应该是每个聚合总数与我在运行单个查询时获得的计数相匹配。

这是我混淆的 table definition对于顺序放置，column_type、char_limit 和 is_nullable。

Answer 1

您的架构绝对令人恐惧:

24  ***_status  text        YES
25  ***_status  text        YES
26  ***_status  text        YES
27  ***_status  text        YES
28  ***_status  text        YES
29  ***_status  text        YES

我假设屏蔽的 *** 类似于出版物/新闻通讯/等的名称。

您需要阅读关于 data normalization 的内容或者你会遇到一个不断增长的问题，直到你点击 PostgreSQL's row-size limit .

由于每个感兴趣的项目都在不同的列中，因此使用现有模式解决此问题的唯一方法是使用 PL/PgSQL 的 EXECUTE format(...) USING ... 编写动态 SQL .您可能认为这只是一个临时选项，但这有点像使用打桩机将方桩塞入圆孔，因为锤子不够大。

SQL 中没有列名通配符，如 *_status 或 %_status。列是行的固定组成部分，具有不同的类型和含义。每当您发现自己希望得到这样的东西时，就表明您的设计需要重新考虑。

我不打算写一个示例，因为 (a) 这是一家电子邮件营销公司，并且 (b) 如果不进行大量手动重写，“混淆”模式完全无法用于任何类型的测试。 (将来，请为您的虚拟数据提供 CREATE TABLE 和 INSERT 语句，或者更好的是 http://sqlfiddle.com/ )。您会在 PL/PgSQL 中找到许多动态 SQL 的示例 - 以及有关如何通过正确使用 format 避免由此产生的 SQL 注入(inject)风险的警告 - 快速搜索 Stack Overflow。我过去写过很多。

请，为了您和其他需要在该系统上工作的人的理智，normalize your schema .

您可以 create a view在规范化表上显示旧结构，让您有时间调整您的应用程序。通过更多的工作，您甚至可以定义一个 DO INSTEAD View 触发器(较新的 Pg 版本)或 RULE (较旧的 Pg 版本)以使 View 可更新和可插入，因此您的应用程序甚至无法判断是否发生了任何变化 - 尽管这是以性能成本为代价的，因此最好尽可能调整应用程序。

从这样的事情开始:

CREATE TABLE subscriber (
    id serial primary key,
    email_address text not null,
    -- please read http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
    -- for why I merged "fname" and "lname" into one field:
    realname text,
    -- Store birth month/year as a "date" with a "CHECK" constraint forcing it to be the 1st day
    -- of the month. Much easier to work with.
    birthmonth date,
    CONSTRAINT birthmonth_must_be_day_1 CHECK ( extract(day from birthmonth) = 1),
    postcode text,
    -- Congratulations! You made "gender" a "text" field to start with, you avoided
    -- one of the most common mistakes in schema design, the boolean/binary gender
    -- field!
    gender text,
    -- What's MSO? Should have a COMMENT ON...
    mso text,
    source text,
    -- Maintain these with a trigger. If you want modified to update when any child record
    -- changes you can do that with triggers on subscription and reducedfreq_subscription.
    created_on timestamp not null default current_timestamp,
    last_modified timestamp not null,
    -- Use the native PostgreSQL UUID type, after running CREATE EXTENSION "uuid-ossp";
    uuid uuid not null,
    uuid2 uuid not null,
    brand text,

    -- etc etc
);

CREATE TABLE reducedfreq_subscription (
    id serial primary key,
    subscriber_id integer not null references subscriber(id),
    -- Suspect this was just a boolean stored as text in your schema, in which case
    -- delete it.
    reducedfreqsub text,
    reducedfreqpref text,
    -- plural, might be a comma list? Should be in sub-table ("join table")
    -- if so, but without sample data can only guess.
    reducedfreqtopics text,
    -- date can be NOT NULL since the row won't exist unless they joined
    reducedfreq_datejoined date not null,
    reducedfreq_dateunsub date
);

CREATE TABLE subscription (
    id serial primary key,
    subscriber_id integer not null references subscriber(id),
    sub_name text not null,
    status text not null,
    datejoined date not null,
    dateunsub date
);

CREATE TABLE subscriber_activity (
    last_click  timestamptz,
    last_open   timestamptz,
    last_hardbounce timestamptz,
    last_softbounce timestamptz,
    last_successful_mailing timestamptz
);