我有一个具有以下结构的表:
name | version | processed | processing | updated | ref_time
------+---------+-----------+------------+----------+----------
abc | 1 | t | f | 27794395 | 27794160
def | 1 | t | f | 27794395 | 27793440
ghi | 1 | t | f | 27794395 | 27793440
jkl | 1 | f | f | 27794395 | 27794160
mno | 1 | t | f | 27794395 | 27793440
pqr | 1 | f | t | 27794395 | 27794160
我可以使用以下查询来计算每个中的总数ref_time
:
SELECT ref_time, COUNT (*) AS total
FROM (SELECT * FROM status_table) AS _
GROUP BY ref_time;
ref_time | total
----------+-------
27794160 | 2259
27793440 | 2259
以及以下查询来计算每个内的总数ref_time
where processed=true
:
SELECT ref_time, COUNT (*) AS processed FROM (SELECT * FROM status_table WHERE processed=true) AS _ GROUP BY ref_time;
ref_time | processed
----------+-----------
27794160 | 1057
27793440 | 2259
然后我尝试使用合并信息INNER JOIN
on ref_time
:
SELECT * FROM
(SELECT ref_time, COUNT (*) AS total
FROM (SELECT * FROM status_table) AS _
GROUP BY ref_time) result_total
INNER JOIN
(SELECT ref_time, COUNT (*) AS processed
FROM (SELECT * FROM status_table WHERE processed=true) AS _
GROUP BY ref_time) result_processed
ON result_total.ref_time = result_processed.ref_time;
ref_time | total | ref_time | processed
----------+-------+----------+-----------
27794160 | 2259 | 27794160 | 1057
27793440 | 2259 | 27793440 | 2259
第一个问题:如何避免重复ref_time
column?
第二个问题:如何添加额外的percent
列派生为(100 * processed / total)
(到一位 d.p.),即给出:
ref_time | total | processed | percent
----------+-------+-----------+---------
27794160 | 2259 | 1057 | 46.8
27793440 | 2259 | 2259 | 100.0
第三个问题:有没有更有效的方法?我可以避免将两个分开吗SELECT
查询?