postgresql - 我想列出所有日期及其各自的数据

Question

我有这个access_log 表，

id      user_id  actionlink_id  create_time          login_status1181    1010     1172           2017-11-02 11:42:29  11182    1010     1172           2017-11-02 11:50:28  01175    1010     1172           2017-11-02 05:50:59  1 1177    1010     1172           2017-11-02 06:19:24  11183    1010     1186           2017-11-03 05:54:17  11185    1010     1186           2017-11-03 07:38:23  11184    1010     1186           2017-11-03 07:38:00  11189    1010     1186           2017-11-03 12:25:40  11188    1010     1186           2017-11-03 12:25:01  11187    1010     1186           2017-11-03 12:24:46  01186    1010     1186           2017-11-03 12:24:17  01190    1010     1186           2017-11-03 12:33:12  01193    1010     1187           2017-11-04 13:44:08  01194    1010     1187           2017-11-04 13:44:18  11195    1010     1187           2017-11-04 13:45:31  01196    1010     1187           2017-11-04 13:46:07  11197    1010     1187           2017-11-04 13:51:59  01198    1010     1187           2017-11-04 13:52:09  1

Ouput from below code

date        to_char2017-11-06  00:04

My Query, this gives only current date values

select current_date,
to_char(
    coalesce (
        ((-- get the logout time for current day
        select 
            create_time 
        from 
            users.access_log 
        where 
            user_id = 1010 
        and login_status = 0 
        and date_trunc('day', create_time) = current_date 
        order by create_time desc limit 1
        ) - (-- get the login time for current day
        select 
            create_time 
        from 
            users.access_log 
        where 
            user_id = 1010 
        and login_status = 1 
        and date_trunc('day', create_time) = current_date 
        order by create_time asc limit 1
        ))
        , (select '0 minutes'::interval) )
        , 'HH24:MI')
        ;

但在这里我想要所有日期的列表

Answer 1

SQL Fiddle

PostgreSQL 9.6 架构设置:

CREATE TABLE access_log
    ("id" int, "user_id" int, "actionlink_id" int, "create_time" timestamp, "login_status" int)
;

INSERT INTO access_log
    ("id", "user_id", "actionlink_id", "create_time", "login_status")
VALUES
    (1181, 1010, 1172, '2017-11-02 11:42:29', 1),
    (1182, 1010, 1172, '2017-11-02 11:50:28', 0),
    (1175, 1010, 1172, '2017-11-02 05:50:59', 1),
    (1177, 1010, 1172, '2017-11-02 06:19:24', 1),
    (1183, 1010, 1186, '2017-11-03 05:54:17', 1),
    (1185, 1010, 1186, '2017-11-03 07:38:23', 1),
    (1184, 1010, 1186, '2017-11-03 07:38:00', 1),
    (1189, 1010, 1186, '2017-11-03 12:25:40', 1),
    (1188, 1010, 1186, '2017-11-03 12:25:01', 1),
    (1187, 1010, 1186, '2017-11-03 12:24:46', 0),
    (1186, 1010, 1186, '2017-11-03 12:24:17', 0),
    (1190, 1010, 1186, '2017-11-03 12:33:12', 0),
    (1193, 1010, 1187, '2017-11-04 13:44:08', 0),
    (1194, 1010, 1187, '2017-11-04 13:44:18', 1),
    (1195, 1010, 1187, '2017-11-04 13:45:31', 0),
    (1196, 1010, 1187, '2017-11-04 13:46:07', 1),
    (1197, 1010, 1187, '2017-11-04 13:51:59', 0),
    (1198, 1010, 1187, '2017-11-04 13:52:09', 1)
;

查询 1:

select
       user_id, actionlink_id, date_trunc('day',create_time) dt
     , min(case when login_status = 1 then create_time end) min_time
     , max(case when login_status = 0 then create_time end) max_time
     , max(create_time) - min(create_time) elapsed_time
from access_log
group by
    user_id, actionlink_id, date_trunc('day',create_time)

Results :

| user_id | actionlink_id |                   dt |             min_time |             max_time |                                     elapsed_time |
|---------|---------------|----------------------|----------------------|----------------------|--------------------------------------------------|
|    1010 |          1186 | 2017-11-03T00:00:00Z | 2017-11-03T05:54:17Z | 2017-11-03T12:33:12Z | 0 years 0 mons 0 days 6 hours 38 mins 55.00 secs |
|    1010 |          1172 | 2017-11-02T00:00:00Z | 2017-11-02T05:50:59Z | 2017-11-02T11:50:28Z | 0 years 0 mons 0 days 5 hours 59 mins 29.00 secs |
|    1010 |          1187 | 2017-11-04T00:00:00Z | 2017-11-04T13:44:18Z | 2017-11-04T13:51:59Z |   0 years 0 mons 0 days 0 hours 8 mins 1.00 secs |