Python psql 分成各自的列

Question

我在确定哪些日子有超过 1% 的状态错误时遇到了一些麻烦，这里是表格:

 path   | text                     |
 ip     | inet                     |
 method | text                     |
 status | text                     |
 time   | timestamp with time zone | default now()
 id     | integer                  | not null default nextval('log_id_seq'::regclass)
Indexes:
    "log_pkey" PRIMARY KEY, btree (id)

重要的部分是状态(200 OK 或错误)和时间(日期)，我相信其余部分对于这个特定问题有点无用

这是我目前的代码:

def heavy_error_days():
    db = psycopg2.connect("dbname=news")
    c = db.cursor()
    c.execute("select date(log.time), errors\
     from log, (select count(status)::numeric/(select count(status)\
     from log)from log where status <> '200 OK'\
     and date(log.time) = date(log.time)) as errors\
     group by date, errors")
    print c.fetchone()

我相信我需要做的是将错误乘以 100 并将它们拆分为各自的日期，但我不知道如何写。

如果有人能帮我解决这个问题，我将不胜感激。

错误

  File "news.py", line 33
    c.execute("with a as (select distinct (sum(case when status <> '200 OK' then 1 else 0 end) over w * 100) / count(1) over w perc, "time"::date d from log window w as (partition by "time"::date))select * from a where perc > 1")
                                                                                                                                         ^
SyntaxError: invalid syntax

我注意到时间用双引号引起来，这可能是问题所在，所以我将其更改为单引号并收到此错误:

Traceback (most recent call last):
  File "news.py", line 41, in <module>
    heavy_error_days()
  File "news.py", line 33, in heavy_error_days
    c.execute("with a as (select distinct (sum(case when status <> '200 OK' then 1 else 0 end) over w * 100) / count(1) over w perc, 'time'::date d from log window w as (partition by 'time'::date))select * from a where perc > 1")
psycopg2.DataError: invalid input syntax for type date: "time"
LINE 1: ...else 0 end) over w * 100) / count(1) over w perc, 'time'::da...
                                                             ^

Answer 1

这个查询应该可以解决问题:

with a as (
  select distinct (sum(case when status <> '200 OK' then 1 else 0 end) over w * 100) / count(1) over w perc, "time"::date d
  from log
  window w as (partition by "time"::date)
)
select *
from a
where perc > 1

基于示例:

构建:

t=# create table log("time" timestamptz, status text);
CREATE TABLE
t=# insert into log values (now(),'200 OK'),(now(),'200 OK'),(now(),'ERR'),(now()-'2 days'::interval,'200 OK');
INSERT 0 4

查询:

t=# with a as (
  select distinct (sum(case when status <> '200 OK' then 1 else 0 end) over w * 100) / count(1) over w perc, "time"::date d
  from log
  window w as (partition by "time"::date)
)
select *
from a
where perc >= 0
;
 perc |     d
------+------------
    0 | 2017-05-17
   33 | 2017-05-19
(2 rows)