sql - 考虑一个组中每个 id 的单个记录

Question

背景

我有一个包含 4 列的 SQL 表:

id - varchar(50)

g1 - varchar(50)

g2 - varchar(50)

datetime - 时间戳

我有这个查询:

SELECT g1,
       COUNT(DISTINCT id),
       SUM(COUNT(DISTINCT id)) OVER () AS total,
       (CAST(COUNT(DISTINCT id) AS float) / SUM(COUNT(DISTINCT id)) OVER ()) AS share
FROM my_table
and g2 = 'start'
GROUP BY 1
order by share desc

此查询旨在回答: g1的分布是什么？用户的值(value)？

问题

每个 id可能有 多个 表中的记录。我想考虑最早的。早意味着，最小值 datetime值(value)。

例子

table

id    g1    g2      datetime
x1    a     start   2016-01-19 21:01:22
x1    c     start   2016-01-19 21:01:21
x2    b     start   2016-01-19 09:03:42
x1    a     start   2016-01-18 13:56:45

实际查询结果

g1  count   total   share
a   2       4       0.5
b   1       4       0.25
c   1       4       0.25

我们有 4 条记录，但我只想考虑两条记录:

x2    b     start   2016-01-19 09:03:42
x1    a     start   2016-01-18 13:56:45

这是每个 id 的最早记录.

预期查询结果

g1  count   total   share
a   1       2       0.5
b   1       2       0.5

题

我如何只考虑最早的记录，每个 id ，在 group by

Answer 1

这是一个适用于 SQL Server 和任何支持 CTE 的数据库的解决方案:

WITH cte AS
(
    SELECT t1.g1,
           COUNT(*) AS count
    FROM yourTable t1
    INNER JOIN
    (
        SELECT id, MIN(datetime) AS datetime
        FROM yourTable
        GROUP BY id
    ) t2
        ON t1.id = t2.id AND
           t1.datetime = t2.datetime
)

SELECT t.g1,
       t.count,
       (SELECT COUNT(*) FROM cte) AS total,
       t.count / (SELECT COUNT(*) FROM cte) AS share
FROM cte t