我有下面的 CTE 来拉入成员资格，然后拉入这些成员的所有索赔(如果他们在日期参数内有索赔命中(，现在从总成员资格 (=1961( 我需要从索赔 CTE 中提取前 3% 或 0.03。我看到 1961*0.03 四舍五入为 59，所以我需要从索赔利用率最高的索赔中提取顶部 (59( 医疗补助 ID。
因此，例如，在提供需要拉入的线路的number_to_pull CTE 中(它提供了 3% 的成员资格(，然后在CTE sum_of_claims中，我只想从索赔CTE 中提取前 3% 的医疗补助 ID。由于成员资格可能会根据日期而变化，我希望sum_of_claims具有如下所示的内容，但我不确定如何开始
最终结果是，我将列出一个在日期跨度内每个索赔点击次数最多的医疗补助 ID 列表顶部 (3%(
我需要这样的东西，但我希望它拉入number_to_pullCTE 中的任何数字，然后根据索赔总和拉出该数字。
Select Top ( select
round(count(mt.medicaid_no)*0.03) as percentt
from membership mt)
cll.medicaid_no
,count(distinct claim_number) as sum_of_claims
from claims cll
Group by cll.medicaid_no
) select * from sum_of_claims
这就是我的代码实际的样子
WITH
DATES AS
(
select TRUNC(TRUNC(SYSDATE,'y')-1,'y') as startdate,
TRUNC(SYSDATE,'y')-1 as enddate
from dual
),
membership as (
select Distinct
mbr.medicaid_no
,mbd.memb_dim_id
,mbd.memb_demographics_full_date
from dw.fact_member_demographics mbd
inner join dates d
on 1=1
inner join dw.DIM_MEMBER mbr
on mbd.memb_dim_id = mbr.memb_dim_id
Where EXTRACT(YEAR FROM mbd.memb_demographics_full_date)= extract(year from d.startdate)
and mbd.company_dim_id in ('575')
and mbd.age > 18
) ---select * from membership
,number_to_pull as (
select
round(count(mt.medicaid_no)*0.03) as percentt
from membership mt
) ---select * from top_number
,Claims as (
select
mbdd.medicaid_no
,mbdd.memb_dim_id
,dc.company_desc
,cl.primary_svc_date
,cl.claim_number
,case when cl.io_flag_dim_id = '1' then 'Inpatient'
when cl.io_flag_dim_id = '2' then 'Outpatient' else 'false' end as In_Op
,cl.admit_type
,proc.procedure_code
,dx1.diagnosis_code as dx1
,dx1.diagnosis_short_desc as dx1desc
,dx2.diagnosis_code as dx2
,dx2.diagnosis_short_desc as dx2desc
,dx3.diagnosis_code as dx3
,dx3.diagnosis_short_desc as dx3desc
,dx4.diagnosis_code as dx4
,dx4.diagnosis_short_desc as dx4desc
,dx5.diagnosis_code as dx5
,dx5.diagnosis_short_desc as dx5desc
,bt.inp_outp_ind
from membership mbdd
left join dw.fact_claim cl
on mbdd.memb_dim_id = cl.memb_dim_id
inner join dates d
on 1=1
inner join dw.DIM_PROCEDURE_CODE proc
on cl.cpt_code_dim_id = proc.procedure_dim_id
inner join dw.DIM_DIAGNOSIS dx1
on cl.diagnosis_1_dim_id = dx1.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx2
on cl.diagnosis_2_dim_id = dx2.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx3
on cl.diagnosis_3_dim_id = dx3.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx4
on cl.diagnosis_4_dim_id = dx4.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx5
on cl.diagnosis_5_dim_id = dx5.diagnosis_dim_id
inner join dw.DIM_BILL_TYPE bt
on cl.bill_type_dim_id = bt.bill_type_dim_id
inner join dw.DIM_COMPANY dc
on cl.company_dim_id = dc.company_dim_id
Where cl.primary_svc_date between d.startdate and d.enddate
and cl.company_dim_id in ('575')
and CL.WHOLE_CLAIM_STATUS_DIM_ID IN (1,2)
and cl.io_flag_dim_id in ('1','2')
) ---select * from claims
,sum_of_claims AS (
Select
---- this is where I want to pull in the top 3% based off of membeship and sum of claims per Medicaid
cll.medicaid_no
,count(distinct claim_number) as sum_of_claims
from claims cll
Group by cll.medicaid_no
) select * from sum_of_claims
我想要的最终结果是医疗补助ID列表和索赔总额，但此列表将仅是前59行(3%(
MEDICAID_NO SUM_OF_CLAIMS
111111 $12,439.61
333333 $5,315.57
444444 $2,007.00
555555 $1,823.98
888888 $1,770.00
777777 $1,211.47
9999999 $1,157.61
6666666 $1,068.76
如果我没看错你的问题，你想在这里获得前 3% 吗？这是最后一个查询：
select * from sum_of_claims;
我认为您想将其替换为以下内容：
SELECT medicaid_no, sum_of_claims FROM (
SELECT medicaid_no, sum_of_claims, COUNT(*) OVER () AS total_cnt
, ROW_NUMBER() OVER ( ORDER BY sum_of_claims DESC ) AS rn
FROM sum_of_claims
) WHERE rn <= 0.03 * total_cnt;
这将获得"前"3%的记录(其中"顶部"定义为索赔金额最大的记录(。
顺便说一句，我发现很难相信这就是你想要的：
,count(distinct claim_number) as sum_of_claims
那根本就不给钱！
希望这有帮助。
谢谢大卫·我能够从索赔中拉入前 3% 的成员.下面是我的代码。
WITH
DATES AS
(
select TRUNC(TRUNC(SYSDATE,'y')-1,'y') as startdate,
TRUNC(SYSDATE,'y')-1 as enddate
from dual
),
membership as (
select Distinct
mbr.medicaid_no
,mbd.memb_dim_id
,mbd.memb_demographics_full_date
from dw.fact_member_demographics mbd
inner join dates d
on 1=1
inner join dw.DIM_MEMBER mbr
on mbd.memb_dim_id = mbr.memb_dim_id
Where EXTRACT(YEAR FROM mbd.memb_demographics_full_date)= extract(year from d.startdate)
and mbd.company_dim_id in ('575')
and mbd.age > 18
) ---select * from membership
,Claims as (
select
mbdd.medicaid_no
,mbdd.memb_dim_id
,dc.company_desc
,cl.primary_svc_date
,cl.claim_number
,case when cl.io_flag_dim_id = '1' then 'Inpatient'
when cl.io_flag_dim_id = '2' then 'Outpatient' else 'false' end as In_Op
,cl.admit_type
,proc.procedure_code
,dx1.diagnosis_code as dx1
,dx1.diagnosis_short_desc as dx1desc
,dx2.diagnosis_code as dx2
,dx2.diagnosis_short_desc as dx2desc
,dx3.diagnosis_code as dx3
,dx3.diagnosis_short_desc as dx3desc
,dx4.diagnosis_code as dx4
,dx4.diagnosis_short_desc as dx4desc
,dx5.diagnosis_code as dx5
,dx5.diagnosis_short_desc as dx5desc
,bt.inp_outp_ind
,cl.net_amt
from membership mbdd
left join dw.fact_claim cl
on mbdd.memb_dim_id = cl.memb_dim_id
inner join dates d
on 1=1
inner join dw.DIM_PROCEDURE_CODE proc
on cl.cpt_code_dim_id = proc.procedure_dim_id
inner join dw.DIM_DIAGNOSIS dx1
on cl.diagnosis_1_dim_id = dx1.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx2
on cl.diagnosis_2_dim_id = dx2.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx3
on cl.diagnosis_3_dim_id = dx3.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx4
on cl.diagnosis_4_dim_id = dx4.diagnosis_dim_id
inner join dw.DIM_DIAGNOSIS dx5
on cl.diagnosis_5_dim_id = dx5.diagnosis_dim_id
inner join dw.DIM_BILL_TYPE bt
on cl.bill_type_dim_id = bt.bill_type_dim_id
inner join dw.DIM_COMPANY dc
on cl.company_dim_id = dc.company_dim_id
Where cl.primary_svc_date between d.startdate and d.enddate
and cl.company_dim_id in ('575')
and CL.WHOLE_CLAIM_STATUS_DIM_ID IN (1,2) -- pulling in only paid claims -- use whole claim
and cl.io_flag_dim_id in ('1','2')
) ---select * from claims
,sum_of_claims AS (
Select
cll.medicaid_no
,sum(distinct cll.net_amt) as sum_of_claims
from claims cll
Group by cll.medicaid_no
)
----- this is the new part added below
SELECT medicaid_no, sum_of_claims FROM (
SELECT sh.medicaid_no, sh.sum_of_claims, COUNT(*) OVER () AS total_cnt
, ROW_NUMBER() OVER ( ORDER BY sh.sum_of_claims DESC ) AS rn
FROM sum_of_claims sh
)
Where (rn)<(SELECT round(count(medicaid_no)*0.03) as percentt
from membership)