如何向此查询添加显示不同行数的列？

Question

我不知道如何清楚地问我的问题，所以我只能给你看钱。

首先，这里有一个示例表：

CREATE TABLE sandbox (
    id integer NOT NULL,
    callsign text NOT NULL,
    this text NOT NULL,
    that text NOT NULL,
    "timestamp" timestamp with time zone DEFAULT now() NOT NULL
);

CREATE SEQUENCE sandbox_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE sandbox_id_seq OWNED BY sandbox.id;

SELECT pg_catalog.setval('sandbox_id_seq', 14, true);

ALTER TABLE sandbox ALTER COLUMN id SET DEFAULT nextval('sandbox_id_seq'::regclass);

INSERT INTO sandbox VALUES (1, 'alpha', 'foo', 'qux', '2010-12-29 16:51:09.897579+00');
INSERT INTO sandbox VALUES (2, 'alpha', 'foo', 'qux', '2010-12-29 16:51:36.108867+00');
INSERT INTO sandbox VALUES (3, 'bravo', 'bar', 'quxx', '2010-12-29 16:52:36.370507+00');
INSERT INTO sandbox VALUES (4, 'bravo', 'foo', 'quxx', '2010-12-29 16:52:47.584663+00');
INSERT INTO sandbox VALUES (5, 'charlie', 'foo', 'corge', '2010-12-29 16:53:00.742356+00');
INSERT INTO sandbox VALUES (6, 'delta', 'foo', 'qux', '2010-12-29 16:53:10.884721+00');
INSERT INTO sandbox VALUES (7, 'alpha', 'foo', 'corge', '2010-12-29 16:53:21.242904+00');
INSERT INTO sandbox VALUES (8, 'alpha', 'bar', 'corge', '2010-12-29 16:54:33.318907+00');
INSERT INTO sandbox VALUES (9, 'alpha', 'baz', 'quxx', '2010-12-29 16:54:38.727095+00');
INSERT INTO sandbox VALUES (10, 'alpha', 'bar', 'qux', '2010-12-29 16:54:46.237294+00');
INSERT INTO sandbox VALUES (11, 'alpha', 'baz', 'qux', '2010-12-29 16:54:53.891606+00');
INSERT INTO sandbox VALUES (12, 'alpha', 'baz', 'corge', '2010-12-29 16:55:39.596076+00');
INSERT INTO sandbox VALUES (13, 'alpha', 'baz', 'corge', '2010-12-29 16:55:44.834019+00');
INSERT INTO sandbox VALUES (14, 'alpha', 'foo', 'qux', '2010-12-29 16:55:52.848792+00');

ALTER TABLE ONLY sandbox
    ADD CONSTRAINT sandbox_pkey PRIMARY KEY (id);

这是我当前的 SQL 查询：

SELECT
    *
FROM
(
    SELECT
        DISTINCT ON (this, that)

        id, this, that, timestamp
    FROM
        sandbox
    WHERE
        callsign = 'alpha'
            AND
        CAST(timestamp AS date) = '2010-12-29'
)
    playground
ORDER BY
    timestamp
DESC

这是它给我的结果：

id      this    that    timestamp
-----------------------------------------------------
14      foo     qux     2010-12-29 16:55:52.848792+00
13      baz     corge   2010-12-29 16:55:44.834019+00
11      baz     qux     2010-12-29 16:54:53.891606+00
10      bar     qux     2010-12-29 16:54:46.237294+00
9       baz     quxx    2010-12-29 16:54:38.727095+00
8       bar     corge   2010-12-29 16:54:33.318907+00
7       foo     corge   2010-12-29 16:53:21.242904+00

这是我想看到的：

id      this    that    timestamp                       count
-------------------------------------------------------------
14      foo     qux     2010-12-29 16:55:52.848792+00   3
13      baz     corge   2010-12-29 16:55:44.834019+00   2
11      baz     qux     2010-12-29 16:54:53.891606+00   1
10      bar     qux     2010-12-29 16:54:46.237294+00   1
9       baz     quxx    2010-12-29 16:54:38.727095+00   1
8       bar     corge   2010-12-29 16:54:33.318907+00   1
7       foo     corge   2010-12-29 16:53:21.242904+00   1

编辑：

我正在使用 PostgreSQL 9.0.* （如果有帮助的话）。

Answer 1

我没有安装postgre，但我在SQL Server上做了这个，我想你可以使用这个想法：

CREATE TABLE sandbox (
    id integer NOT NULL,
    callsign varchar(1000) NOT NULL,
    this varchar(1000) NOT NULL,
    that varchar(1000) NOT NULL,
    tm datetime NOT NULL
);


INSERT INTO sandbox VALUES (1, 'alpha', 'foo', 'qux', '2010-12-29 16:51:09');
INSERT INTO sandbox VALUES (2, 'alpha', 'foo', 'qux', '2010-12-29 16:51:36');
INSERT INTO sandbox VALUES (3, 'bravo', 'bar', 'quxx', '2010-12-29 16:52:36');
INSERT INTO sandbox VALUES (4, 'bravo', 'foo', 'quxx', '2010-12-29 16:52:47');
INSERT INTO sandbox VALUES (5, 'charlie', 'foo', 'corge', '2010-12-29 16:53:00');
INSERT INTO sandbox VALUES (6, 'delta', 'foo', 'qux', '2010-12-29 16:53:10');
INSERT INTO sandbox VALUES (7, 'alpha', 'foo', 'corge', '2010-12-29 16:53:21');
INSERT INTO sandbox VALUES (8, 'alpha', 'bar', 'corge', '2010-12-29 16:54:33');
INSERT INTO sandbox VALUES (9, 'alpha', 'baz', 'quxx', '2010-12-29 16:54:38');
INSERT INTO sandbox VALUES (10, 'alpha', 'bar', 'qux', '2010-12-29 16:54:46');
INSERT INTO sandbox VALUES (11, 'alpha', 'baz', 'qux', '2010-12-29 16:54:53');
INSERT INTO sandbox VALUES (12, 'alpha', 'baz', 'corge', '2010-12-29 16:55:39');
INSERT INTO sandbox VALUES (13, 'alpha', 'baz', 'corge', '2010-12-29 16:55:44');
INSERT INTO sandbox VALUES (14, 'alpha', 'foo', 'qux', '2010-12-29 16:55:52');

    SELECT  max(id), this, that, min(tm), COUNT(*)
    FROM sandbox
    WHERE
        callsign = 'alpha'
      AND
        CAST(tm AS date) = '2010-12-29'
    GROUP BY this, that
    ORDER BY MAX(id) DESC

输出：

14  foo qux 2010-12-29 16:51:09.000 3
13  baz corge   2010-12-29 16:55:39.000 2
11  baz qux 2010-12-29 16:54:53.000 1
10  bar qux 2010-12-29 16:54:46.000 1
9   baz quxx    2010-12-29 16:54:38.000 1
8   bar corge   2010-12-29 16:54:33.000 1
7   foo corge   2010-12-29 16:53:21.000 1

Answer 2

DCP 解决方案有效。在 postgres 上测试过。

顺便说一句，您应该避免使用时间戳作为列名，因为它是保留字并且需要加引号。