Extract biopsy info and calculate NCCN Risk stratification

What is NCCN Risk stratification?

 

Risk stratification to guide molecular testing and treatment is emphasized in the latest prostate cancer guidelines from the National Comprehensive Cancer Network (NCCN)

https://www.targetedonc.com/view/nccn-prostate-cancer-guidelines-emphasize-risk-stratification

NCCN Prostate Cancer Guidelines Emphasize Risk Stratification

 

Click and popup "Table. Risk stratification and Stageing Workup of Prostate Cancer' And take a look at Clinical/Pathologic features of Riski Group - Very Low! "...cores positives, < 50% cancer ..."

 

 

Biopsy Report 

 

Biopsy Reporsts are so-called "unstuctred data"

 

https://www.slideshare.net/KaranJian/ihc-report-biopsy

https://blog.naver.com/jfeelgood/222595026391

 

Extract Max Percent

 

Supose there is a biopsy report that contains cores information as follows.

tumor site; Rt1, Rt3, Rt4, Lt2 (4/12 positive cores)  (Rt1; 0.5/1.4 cm, Rt3; 0.5/1.3 cm, Rt4; 0.5/1.2 cm, Lt2; 0.5/0.9 cm)

 

And you need to query and find out max percent 56%! That is used for NCCN Risk Stratification.

0.5/1.4 = 36%
0.5/1.3 = 38%
0.5/1.2 = 42%
0.5/0.9 = 56% 

 

 

to be continued...

 

 

 

 

 

 

 

 

 

 

 

tumor site; Rt1, Rt3, Rt4, Rt5, Rt6, Lt1, Lt2, Lt5, Lt6 (9/12 positive cores)

       (Rt1; 0.5/1.4 cm, Rt3; 0.5/1.3 cm, Rt4; 0.5/1.2 cm, Rt5; 0.5/0.9 cm, Rt6; 0.5/0.5 cm,...

 

Regular Expression​ / Window function / Hierarchy query

1
2
3

 

1. NCCN Risk?

2. positive core %

3. how to calculate ?

 

4. Regular Expression 

0.5/1.4 => [0-9.]{1,4}/[0-9.]{1,4}

5. get max

 

https://cafe.naver.com/pcainfo/26996

https://cafe.naver.com/pcainfo/7145

https://blog.naver.com/jfeelgood/222595026391

googling 키워드 : connect by level oracle sql
여러가지 대안은 제시되고 있으나 왜 그런지 이유에 대한 답은 없다.
https://community.oracle.com/tech/developers/discussion/2207197/how-to-use-connect-by-level-to-get-this-result
​
** 설명이 가장 명쾌하다
When connect by is used without start with clause and prior operator,
there is no restriction on joining children row to a parent row.
And what Oracle does in this situation,
it returns all possible hierarchy permutations by connecting a row to every row of level higher.
​
​
https://stackoverflow.com/questions/13540661/why-does-connect-by-level-on-a-table-return-extra-rows
​
순열 P​

 

----------------------------------------
-- 1,2,2 는 왜 두번 나올까?
----------------------------------------
select n,dup_cnt,level
from
(
select 1 n, 2 dup_cnt  from dual union all
select 2 n, 3 dup_cnt  from dual 
)
connect by level <= dup_cnt
order by n, level
 
 
 
----------------------------------------
-- 간단한 방법은 distinct 해주면 된다.
----------------------------------------
select distinct n,dup_cnt,level
from
(
select 1 n, 2 dup_cnt  from dual union all
select 2 n, 3 dup_cnt  from dual 
)
connect by level <= dup_cnt
order by n, level