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

Question

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

除了这些栏目之外，我还有一个主要的疑犯栏目，那是我们的客户服务部。可以更新以适应希望从我们所有邮件中删除的愤怒的订阅者，以及另一个列，该列在发生硬退回(或设置数量的软退回)时进行更新，称为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。

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

下面是我的用于顺序放置、column_type、char_limit和is_nullable的模糊table definition。

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/语句)。通过快速搜索Stack Overflow，您将在PL/PgSQL中找到大量动态SQL的示例，以及有关如何通过正确使用format来避免由此产生的SQL注入风险的警告。我以前写过很多东西。

为了你的理智和其他需要在这个系统上工作的人的理智，normalize your schema。

您可以对规格化表执行create a view操作以显示旧结构，从而有时间调整您的应用程序。再多做一点工作，你甚至可以定义一个DO INSTEAD视图触发器(较新的Pg版本)或RULE (较旧的Pg版本)，以使视图可更新和可插入，因此您的应用程序甚至无法判断是否发生了任何更改-尽管这是以性能为代价的，因此如果可能的话，最好调整应用程序。

从下面这样开始：

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

Answer 2

仅仅把它称为“可怕的”，显示了你的机智和善意。谢谢。:)我最近才继承了这个模式(它最初是由StrongMail的人创建的)。

我今年的路线图上有一个完整的关系型数据库重新搜索项目--样本规范化与我一直在做的工作非常一致。关于realname的见解非常有趣，我还没有真正考虑过这一点。我想，StrongMail之所以能做到这一点，唯一的原因就是为了实现电子邮件的名字个性化。

MSO是多系统运营商(有线电视公司)。我们是一家大型的生活方式媒体公司，我们制作的时事通讯涉及食物、旅行、住宅和园艺。

我正在为此创建一个小提琴-我是新来的，所以在下一步，我会更加注意你们需要帮助的东西。谢谢!