问题描述
我有一个看起来像这样的模型:
I have a Model that looks like:
MedicationAdherence {
:id => :integer,
:adherence_date => :date,
:scheduled_time => :string,
:acknowledged_at => :datetime,
:patient_id => :integer,
:created_at => :datetime,
:updated_at => :datetime
}
我有7条记录(与patient_id
相同):
I have 7 records (same patient_id
):
{ id: 1, adherence_date: 2017-10-01, scheduled_time: 'morning', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 2, adherence_date: 2017-10-01, scheduled_time: 'afternoon', acknowledged_at: nil }
{ id: 3, adherence_date: 2017-10-01, scheduled_time: 'night', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 4, adherence_date: 2017-10-02, scheduled_time: 'morning', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 5, adherence_date: 2017-10-02, scheduled_time: 'afternoon', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 6, adherence_date: 2017-10-02, scheduled_time: 'evening', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 7, adherence_date: 2017-10-02, scheduled_time: 'night', acknowledged_at: nil }
我想要的结果是将上面的记录分组为以下输出:
My desired outcome is to group the records above into the following output:
{
"adherence_date" => 2017-10-1,
"morning" => 1,
"afternoon" => 0,
"evening" => nil,
"night" => 1
},
{
"adherence_date" => 2017-10-2,
"morning" => 1,
"afternoon" => 1,
"evening" => 1,
"night" => 0
}
如果没有记录(2017年10月1日晚上),则应返回nil.当有记录但没有Confirmed_at时,它应该返回false
(0),而当有Confirmed_at时,它返回true
(1)
When there is no-record (evening 2017-10-1) it should return nil. When there is a record but no acknowledged_at it should return false
(0), and when there is acknowledged_at returns true
(1)
下面是我用来尝试合并所有这些数据的查询,但是它给了我重复的记录.如何将我的数据汇总到上面的数据中...我敢肯定有一种更简单的方法可以做到这一点
Below is the query I used to try and combine all this data, but it gives me duplicate records. How can I sum my data into what I have above...I'm sure there's a simplier way to do this
WITH
adherences AS (
SELECT * FROM medication_adherences WHERE patient_id = 10049
),
morning AS (
SELECT adherence_date,
CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as morning
FROM adherences
WHERE scheduled_time = 'morning'
),
afternoon as (
SELECT adherence_date,
CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as afternoon
FROM adherences
WHERE scheduled_time = 'afternoon'
),
evening as (
SELECT adherence_date,
CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as evening
FROM adherences
WHERE scheduled_time = 'evening'
),
night as (
SELECT adherence_date,
CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as night
FROM adherences
WHERE scheduled_time = 'night'
)
SELECT morning.morning, afternoon.afternoon, evening.evening, night.night, adherences.adherence_date
FROM adherences
LEFT JOIN morning ON morning.adherence_date = adherences.adherence_date
LEFT JOIN afternoon ON afternoon.adherence_date = adherences.adherence_date
LEFT JOIN evening ON evening.adherence_date = adherences.adherence_date
LEFT JOIN night ON night.adherence_date = adherences.adherence_date
我正在运行oracle-12c
编辑
好像我必须在查询中添加GROUP BY morning.morning, afternoon.afternoon, evening.evening, night.night, adherences.adherence_date
才能正确分组.有没有更简单的方法来汇总这些数据?
Looks like I had to add GROUP BY morning.morning, afternoon.afternoon, evening.evening, night.night, adherences.adherence_date
to my query for it to properly group by. Is there a simpler way to aggregate this data?
推荐答案
我假设(patient_id, adherence_date, scheduled_time)
在您的表格中是唯一的,这意味着患者可以在每个时段"和日期中预订一次.
I'm assuming that (patient_id, adherence_date, scheduled_time)
is unique in your table, meaning that a patient can book once per "slot" and date.
with medication_adherences as(
-- This is your test data
select 10049 as patient_id, 1 as id, date '2017-10-01' as adherence_date, 'morning' as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
select 10049 as patient_id, 2 as id, date '2017-10-01' as adherence_date, 'afternoon' as scheduled_time, null as acknowledged_at from dual union all
select 10049 as patient_id, 3 as id, date '2017-10-01' as adherence_date, 'night' as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
select 10049 as patient_id, 4 as id, date '2017-10-02' as adherence_date, 'morning' as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
select 10049 as patient_id, 5 as id, date '2017-10-02' as adherence_date, 'afternoon' as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
select 10049 as patient_id, 6 as id, date '2017-10-02' as adherence_date, 'evening' as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
select 10049 as patient_id, 7 as id, date '2017-10-02' as adherence_date, 'night' as scheduled_time, null as acknowledged_at from dual
)
select adherence_date
,sum(case when scheduled_time = 'morning' then nvl2(acknowledged_at,1,0) end) as morning
,sum(case when scheduled_time = 'afternoon' then nvl2(acknowledged_at,1,0) end) as afternoon
,sum(case when scheduled_time = 'evening' then nvl2(acknowledged_at,1,0) end) as evening
,sum(case when scheduled_time = 'night' then nvl2(acknowledged_at,1,0) end) as night
from medication_adherences
where patient_id = 10049
group
by adherence_date;
逻辑原理如下:
- 如果accepted_at为null,则我们将汇总0(通过nvl2)
- 如果accepted_at为不为空,则我们汇总1(通过nvl2)
- 如果此时隙没有记录,我们将合计null(因为...失败的情况)
- if acknowledged_at is null then we aggregate 0 (via nvl2)
- if acknowledged_at is not null then we aggregate 1 (via nvl2)
- if there is no record for this timeslot we aggregate null (since case when ... failed)
这篇关于按日期汇总多个记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!