我的目标是使用左连接匹配表和表xx的计数。到目前为止，我得到更高的计数表xx和不确定如何降低他们。表的计数约为2.1 mil，表xx的计数约为2.5 mil。所以我需要xx表的计数精确到210万。我不能正确连接或内部连接，因为我将删除计数从，我需要（他们将太低-我不能只是放弃我的丢失）。我还能做什么？

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';