代码之家 › 专栏 › 技术社区 › ytu

从出现次数超过n次的联接表中选择行

count group-by join postgresql

ytu · 技术社区 · 8 年前

我的问题和 MySQL: Select rows with more than one occurrence 但我使用的是PostgreSQL。我有这样一个问题:

select d.user_id, d.recorded_at, d.glucose_value, d.unit
from diary as d
join (
    select u.id
    from health_user as u
    join (
        select distinct user_id
        from care_connect
        where clinic_id = 217
            and role = 'user'
            and status = 'active'
    ) as c
    on u.id = c.user_id
    where u.is_tester is false
) as cu
on d.user_id = cu.id
where d.created_at >= d.recorded_at
    and d.recorded_at < current_date and d.recorded_at >= current_date - interval '30 days'
    and d.glucose_value > 0
    and (d.state = 'wakeup' or (d.state = 'before_meal' and d.meal_type = 'breakfast'))

结果如下:

+---------+---------------------+---------------+--------+
| user_id |     recorded_at     | glucose_value |  unit  |
+---------+---------------------+---------------+--------+
|   12041 | 2018-06-26 01:10:12 |           100 | mg/dL  |
|   12041 | 2018-06-30 02:10:11 |            90 | mg/dL  |
|   12214 | 2018-06-25 12:40:13 |            10 | mmol/L |
|   12214 | 2018-06-26 12:41:13 |            12 | mmol/L |
|   12214 | 2018-06-29 00:21:14 |            11 | mmol/L |
|   12214 | 2018-06-29 12:59:32 |            10 | mmol/L |
+---------+---------------------+---------------+--------+

如您所见,这已经是一个具有许多条件的长查询。现在,我只想从结果中包含不少于四个记录(行)的用户那里获取记录,因此我尝试了:

select d.user_id, d.recorded_at, d.glucose_value, d.unit, count(d.*)
from diary as d
join (
    select u.id
    from health_user as u
    join (
        select distinct user_id
        from care_connect
        where clinic_id = 217
            and role = 'user'
            and status = 'active'
    ) as c
    on u.id = c.user_id
    where u.is_tester is false
) as cu
on d.user_id = cu.id
where d.created_at >= d.recorded_at
    and d.recorded_at < current_date and d.recorded_at >= current_date - interval '30 days'
    and d.glucose_value > 0
    and (d.state = 'wakeup' or (d.state = 'before_meal' and d.meal_type = 'breakfast'))
group by d.user_id
having count(d.*) >= 4

我期望的输出是:

+---------+---------------------+---------------+--------+
| user_id |     recorded_at     | glucose_value |  unit  |
+---------+---------------------+---------------+--------+
|   12214 | 2018-06-25 12:40:13 |            10 | mmol/L |
|   12214 | 2018-06-26 12:41:13 |            12 | mmol/L |
|   12214 | 2018-06-29 00:21:14 |            11 | mmol/L |
|   12214 | 2018-06-29 12:59:32 |            10 | mmol/L |
+---------+---------------------+---------------+--------+

但是,它抛出了一个错误,说 d.recorded_at 也应添加到 group by 但这不是我想要的。此外,对原始时间戳进行分组也没有意义。

我知道我可以加入另一个表,这个表是由同一个查询生成的,但是只有 select d.user_id, count(d.*) 在第一行,但是整个查询看起来都很疯狂。

有人能帮我更好地实现这一点吗?对不起,我没有把表结构放在这里,但是如果需要,我可以编辑和澄清。

2 回复 | 直到 8 年前

Gaj 8 年前

Select user_id, recorded_at, glucose_value, unit
From (
select d.user_id, d.recorded_at, d.glucose_value, d.unit, count(1) over (partition by d.user_id) rcnt
from diary as d
join (
    select u.id
    from health_user as u
    join (
        select distinct user_id
        from care_connect
        where clinic_id = 217
            and role = 'user'
            and status = 'active'
    ) as c
    on u.id = c.user_id
    where u.is_tester is false
) as cu
on d.user_id = cu.id
where d.created_at >= d.recorded_at
    and d.recorded_at < current_date and d.recorded_at >= current_date - interval '30 days'
    and d.glucose_value > 0
    and (d.state = 'wakeup' or (d.state = 'before_meal' and d.meal_type = 'breakfast'))
) x 
Where rcnt >= 4

Fahad Anjum 8 年前

with original_query as ( your_query )
select * from original_query q1
where 
exists( select q2.user_id from original_query q2 where q1.user_id = q2.user_id
group by q2.user_id 
having count(q2.user_id) >= 4 )