sql - distinct 子句不会产生预期的结果
问题描述
我想为每个不同的 hadm_id 设置一行，这是我的查询：
select
DISTINCT (hadm_id) as Admission_ID,
subject_id as Patient_ID,
icustay_id as Care_Unit_ID ,
intime as In_Time ,
outtime Out_Time,
age as Age,
heartrate_max as Heartrate_max,
-- heartrate_min as Heartrate_min,
sysbp_max as Systolic_Blood_Pressure_max,
-- sysbp_min as Systolic_Blood_Pressure_min,
tempc_max as Temperature_max,
-- tempc_min as Temperature_min,
pao2fio2_vent_min as PAO2_FIO2,
urineoutput as Urine_Otput,
-- bun_min as Blood_Urea_Nitrogen_min,
bun_max as Blood_Urea_Nitrogen_max,
wbc_min as White_Blood_Cells_min,
-- wbc_max as White_Blood_Cells_max,
-- potassium_min as Potassium_min,
potassium_max as Potassium_max,
-- sodium_min as Sodium_min,
sodium_max as Sodium_max,
-- bicarbonate_min as Bicarbonate_min,
bicarbonate_max as Bicarbonate_max,
-- bilirubin_min as Bilirubin_min,
bilirubin_max as Bilirubin_max,
mingcs as mingcs, aids as aids,
hem as hem,
mets as mets,
admissiontype as admissiontype,
resprate_max as resprate_max,
pco2 as pco2,
infection as infection,
organ_dysfunction as organ_dysfunction
from sepsiscategories
-- where hadm_id in (select DISTINCT(hadm_id) from sepsiscategories) and subject_id in (select DISTINCT(subject_id) from sepsiscategories)
-- group by
-- hadm_id,subject_id
order by hadm_id asc
limit 100
结果不是我所期望的：
admission_id | patient_id | care_unit_id | in_time | out_time | age | heartrate_max | systolic_blood_pressure_max | temperature_max | pao2_fio2 | urine_otput |> blood_urea_nitrogen_max | white_blood_cells_min | potassium_max | sodium_max | bicarbonate_max | bilirubin_max | mingcs | aids | hem | mets | admissiontype | resprate_max | pco2 | inf
100003 | 54610 | 209281 | 2150-04-17 15:35:42 | 2150-04-19 14:12:52 | 59.91 | 104 | 146 | 36.7777777777778 | | 2580 | 51 | 13.4 | 5.7 | 133 | 21 | 5.5 | 14 | | | | Medical | 21 | 29 | 0 | 0
100006 | 9895 | 291788 | 2108-04-06 15:50:15 | 2108-04-11 15:18:03 | 48.92 | 127 | 149 | 36.5555572509766 | | 2950 | 16 | 9.6 | 4.4 | 131 | 24 | | 15 | | 1 | | Medical | 29 | 37 | 1 | 0
100006 | 9895 | 291788 | 2108-04-06 15:50:15 | 2108-04-11 15:18:03 | 48.92 | 127 | 149 | 36.5555572509766 | | 2950 | 16 | 9.6 | 4.4 | 131 | 24 | | 15 | | 1 | | Medical | 29 | 40 | 1 | 0
100006 | 9895 | 291788 | 2108-04-06 15:50:15 | 2108-04-11 15:18:03 | 48.92 | 127 | 149 | 36.5555572509766 | | 2950 | 16 | 9.6 | 4.4 | 131 | 24 | | 15 | | 1 | | Medical | 29 | 48 | 1 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 30 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 35 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 37 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 39 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 40 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 41 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 44 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 45 | 0 | 0
100009 | 533 | 253656 | 2162-05-17 10:18:31 | 2162-05-19 22:05:14 | 60.80 | 92 | 132 | | 126 | 2490 | 13 | 12.3 | 4.9 | 143 | 24 | | 15 | | | | UnscheduledSurgical | 37 | 59 | 0 | 0
100011 | 87977 | 214619 | 2177-08-29 04:52:21 | 2177-09-09 16:56:35 | 21.50 | 142 | 165 | 38.7222222222222 | 308.333333333333 | 5160 | 12 | 7.6 | 4.5 | 144 | 24 | | 15 | | | | Medical | 30 | 33 | 1 | 0
100011 | 87977 | 214619 | 2177-08-29 04:52:21 | 2177-09-09 16:56:35 | 21.50 | 142 | 165 | 38.7222222222222 | 308.333333333333 | 5160 | 12 | 7.6 | 4.5 | 144 | 24 | | 15 | | | | Medical | 30 | 35 | 1 | 0
100011 | 87977 | 214619 | 2177-08-29 04:52:21 | 2177-09-09 16:56:35 | 21.50 | 142 | 165 | 38.7222222222222 | 308.333333333333 | 5160 | 12 | 7.6 | 4.5 | 144 | 24 | | 15 | | | | Medical | 30 | 47 | 1 | 0
我该怎么做才能解决它。
解决方案
SELECT DISTINCT对整个数据集进行操作，而不是对特定列进行操作。因此，查询返回的所有列中的所有值的每个不同组合都会获得一条记录。
由于您使用的是 Postgres，因此您可能想尝试DISTINCT ON. 这为您在 ON 子句中定义的每个组提供了一条记录。这些ORDER BY子句确定应在每个组中返回哪一行。
在您的查询中：
select
distinct on(hadm_id) hadm_id as Admission_ID,
subject_id as Patient_ID,
icustay_id as Care_Unit_ID ,
...
from sepsiscategories
order by hadm_id, intime
limit 100
这将为您提供每条hadm_id记录，其中包含最小的记录intime。您需要order by为您的用例调整具有正确列的子句。
推荐阅读
- angular - 在来自 http GET、Angular 6 的输入字段中显示对象
- sql - 如何禁用特定 Pervasive 用户帐户的存储过程查看权限？
- c# - 如何在 swagger 中修改关联数组、哈希或字典中的键以获取文档？
- python - 将查询参数添加到 URL
- python - 递增增加列表中的非零元素
- python - 使用 PIL 使文本大小自动调整为图像
- sql-server - SQL Server：不同表中列之间的关系
- javascript - 多次更改段落以匹配幻灯片中更改的图像
- php - magento 1.9.3.9 中损坏的模块块我如何调试问题
- machine-learning - 如何纠正来自外部环境的传感器漂移？