postgresql - 在 SQL 中编写子查询

Question

我正在学习 PostgreSQL，我有一个数据库日志，其中包含以下列:路径文本、ip inet、方法文本、状态文本、时间 timestamptz、id int

使用此数据库，我的目标是找到任何错误百分比(状态!=“200 OK”/状态)超过 2.5% 的日子。我有一个查询，如下所示，它以小数形式得到误差百分比，但我不知道如何将它们乘以 10 使它们成为“百分比”，也不知道如何检查它们是否超过 2.5。

我在多个地方都尝试过 HAVING 和 WHERE，但是我要么在 group by 处遇到编程错误，要么在我输入 HAVING 或 WHERE 的任何地方遇到语法错误。将其更改为百分比并检查其是否超过 2.5 的正确方法是什么？提前致谢

模拟数据:

CREATE TABLE log (
    path text,
    ip inet,
    method text,
    status text,
    "time" timestamp with time zone DEFAULT now(),
    id integer NOT NULL
);

INSERT INTO log VALUES ('/article/bears-love-berries', '198.51.100.76' , 'GET', '200 OK', '2016-07-1 12:54:22+00', 3355597);
INSERT INTO log VALUES ('/article/candidate-is-jerk', '198.51.100.76' , 'GET', '200 OK', '2016-07-1 19:54:22+00', 3355598);
INSERT INTO log VALUES ('/article/bears-love-berries', '198.51.100.76' , 'GET', '404 NOT FOUND', '2016-07-1 22:54:22+00', 3355599);
INSERT INTO log VALUES ('/article/goats-eat-googles', '203.0.113.42' , 'GET', '200 OK', '2016-07-11 12:54:22+00', 3355600);
INSERT INTO log VALUES ('/article/candidate-is-jerk', '203.0.113.42' , 'GET', '200 OK', '2016-07-11 17:54:22+00', 3355601);
INSERT INTO log VALUES ('/article/bears-love-berries', '203.0.113.42' , 'GET', '200 OK', '2016-07-11 19:54:22+00', 3355602);
INSERT INTO log VALUES ('/article/goats-eat-googles', '203.0.113.42' , 'GET', '404 NOT FOUND', '2016-07-15 13:54:22+00', 3355603);
INSERT INTO log VALUES ('/article/bears-love-berries', '198.51.100.34' , 'GET', '200 OK', '2016-07-15 20:54:22+00', 3355604);
INSERT INTO log VALUES ('/article/candidate-is-jerk', '198.51.100.34' , 'GET', '200 OK', '2016-07-15 23:54:22+00', 3355605);
INSERT INTO log VALUES ('/article/candidate-is-jerk', '203.0.113.12' , 'GET', '200 OK', '2016-07-21 19:54:22+00', 3355606);
INSERT INTO log VALUES ('/article/bears-love-berries', '203.0.113.12' , 'GET', '200 OK', '2016-07-21 12:54:22+00', 3355607);
INSERT INTO log VALUES ('/article/goats-eat-googles', '198.51.100.76' , 'GET', '200 OK', '2016-07-21 01:54:22+00', 3355608);
INSERT INTO log VALUES ('/article/goats-eat-googles', '198.51.100.76' , 'GET', '404 NOT FOUND', '2016-07-29 07:54:22+00', 3355609);
INSERT INTO log VALUES ('/article/candidate-is-jerk', '198.51.100.76' , 'GET', '404 NOT FOUND', '2016-07-29 20:54:22+00', 3355610);
INSERT INTO log VALUES ('/article/goats-eat-googles', '198.51.100.34' , 'GET', '200 OK', '2016-07-29 19:54:22+00', 3355611);

查询:

SELECT DATE(time) AS day,
(
  (SELECT CAST(COUNT(status) AS FLOAT)
  FROM log WHERE status != '200 OK') /
  (SELECT CAST(COUNT(status) AS FLOAT))
) AS views
FROM log GROUP BY day
ORDER BY views

Answer 1

在 sqlfiddle 中分享了一个测试用例后，我们可以开始使用运行代码来完成它。

fiddle 在这里:http://sqlfiddle.com/#!15/b3ed9/39

除了将结果乘以 100，您还可以检查已经乘以的 0.025，不是吗？我的意思是，如果您的值介于 0-1 之间，则相当于 2.5% 将仅为 0.025。

因此该命令应该这样做:

select views.day, views.cnt from
 (select datelog.day,
  (
   cast(count(case when datelog.status != '200 OK' then 1 else null end) as float)
   /
   cast(count(*) as float)
  ) as cnt
  from (select DATE(time) as day, status from log) as datelog
  group by datelog.day
 ) as views
 where views.cnt > 0.025

最后一次修改深深地建立在这里的回复:SQL use column from subselect in where clause