sql - 当我加入两个表时，我试图将要加入的表的计数与新表的计数相匹配

Question

我的目标是使用左连接将表 xxx 的计数与表 xx 相匹配。到目前为止，我从表 xx 中获得了更高的计数，并且不确定如何降低它们。表 xxx 的计数约为 210 万，表 xx 的计数约为 250 万。所以我需要表 xx 的计数完全匹配 ~210 万。我不能正确加入或内部加入，因为我会从我需要的 xxx 中删除计数（它们太低了——我不能只删除我的缺失值）。我还可以做些什么？

drop table if exists x;
CREATE TABLE x AS
SELECT * from (select DISTINCT ROW_NUMBER() OVER (PARTITION BY patient_guid ORDER BY procedure_code) AS rn,
patient_guid,
procedure_date AS effective_date,
procedure_code AS practice_code,
CASE WHEN procedure_code ILIKE '%aaa%' THEN
'Aqueous Shunt'
WHEN procedure_code ILIKE '%aaa%' THEN
'Aqueous Shunt, revision'
WHEN procedure_code ILIKE '%aaa%' THEN
'Canaloplasty with stent'
WHEN procedure_code ILIKE '%aaa%' THEN
'Canaloplasty without stent'
WHEN procedure_code ILIKE '%aaa%' THEN
'Cataract surgery'
WHEN procedure_code ILIKE '%aaa%' THEN
'Endoscopic cyclophotocoagulation'
WHEN procedure_code ILIKE '%aaa%' THEN
'ExPress shunt'
WHEN procedure_code ILIKE '%aaa%' THEN
'Goniotomy/Trabeculotomy'
WHEN procedure_code ILIKE '%aaa%' THEN
'Laser trabeculoplasty'
WHEN procedure_code ILIKE '%aaa%' THEN
'Postoperative revisions' 
WHEN procedure_code ILIKE '%aaa%' THEN
'Removal of device' 
WHEN procedure_code ILIKE '%aaa%' THEN
'Suprachoroidal bypass, Cypass'
WHEN procedure_code ILIKE '%aaa%' THEN
'Trabecular bypass, iStent/Hydrus'
WHEN procedure_code ILIKE '%aaa%' THEN
'Trabeculectomy'
WHEN procedure_code ILIKE '%aaa%' THEN
'Trabeculectomy, Revision'
WHEN procedure_code ILIKE '%aaa%' THEN
'Transscleral cyclophotocoagulation'
WHEN procedure_code ILIKE '%aaa%' THEN
'central corneal thickness measurement'
WHEN procedure_code ILIKE '%aaa%' THEN
'gonioscopy'
WHEN procedure_code ILIKE '%aaa%' THEN
'optic nerve/nerve fiber layer imaging'
WHEN procedure_code ILIKE '%aaa%' THEN
'visual field testing'
WHEN procedure_code IS NULL THEN
'Missing'
ELSE
procedure_description
END AS category
FROM
madrid2.patient_procedure
WHERE (
category ILIKE '%Aqueous Shunt%'
OR category ILIKE '%Aqueous Shunt, revision%'
OR category ILIKE '%Canaloplasty with stent%'
OR category ILIKE '%Canaloplasty without stent%'
OR category ILIKE '%Cataract surgery%'
OR category ILIKE '%Endoscopic cyclophotocoagulation%'
OR category ILIKE '%ExPress shunt%'
OR category ILIKE '%Goniotomy/Trabeculotomy%'
OR category ILIKE '%Laser trabeculoplasty%'
OR category ILIKE '%Postoperative revisions%'
OR category ILIKE '%Removal of device%'
OR category ILIKE '%Suprachoroidal bypass, Cypass%'
OR category ILIKE '%Trabecular bypass, iStent/Hydrus%'
OR category ILIKE '%Trabeculectomy%'
OR category ILIKE '%Trabeculectomy, Revision%'
OR category ILIKE '%Transscleral cyclophotocoagulation%'
OR category ILIKE '%central corneal thickness%'
OR category ILIKE '%gonioscopy%'
OR category ILIKE '%optic nerve fiber layer%'
OR category ILIKE '%visual field%')
AND practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%aaa%'
OR practice_code ILIKE '%v%'
AND effective_date BETWEEN '2013-01-01 00:00:00'
AND '2019-12-31 00:00:00')
where rn = '1';

drop table if exists xx;
create table xx as
select * from (
    select DISTINCT
        'proc' as category2,
        proc.category,
        u.patient_guid,
        count(distinct proc.effective_date) as cnt,
        ROW_NUMBER() OVER (PARTITION BY u.patient_guid ORDER BY category) AS rn
    from
        xxx u
        LEFT join x on proc.patient_guid = u.patient_guid
        and datediff(days, u.index_date, proc.effective_date) between 0
        and 365
        and academic = '1'
    group by
        1,
        2,
        3)
        where rn = '1';

标签： sqlsql-serverdatabaseleft-join