Insights into Cross Join Rank Functions in Health Informatics Program at GMU

undefined
Rank Order Function
Farrokh Alemi, Ph.D.
Cross
Join
Cross
Join
Rank &
Rank_Dense
Cross
Join
Rank &
Rank_Dense
Cross
Join
Rank &
Rank_Dense
Cross
Join
ID
 
icd9
  
Age 
  
Rank
10
 
I276.1
 
63.16
 
1
10
 
I276.1
 
64.08
 
2
10
 
I276.1
 
64.25
 
3
10
 
I276.1
 
64.25
 
3
10
 
I276.1
 
64.33
 
5
10
 
I276.1
 
64.66
 
6
10
 
I276.1
 
64.75
 
7
Skip 4
Cross
Join
Rank &
Rank_Dense
Advice:
If it makes sense,
delete repeated
entries
Cross
Join
Rank &
Rank_Dense
Advice:
Patient having
same diagnosis at
same time
Cross
Join
Rank Function Syntax
RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Cross
Join
Rank Function Syntax
RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Cross
Join
Rank Function Syntax
RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Cross
Join
Rank Function Syntax
RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Cross
Join
Rank Function Syntax
RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Cross
Join
Rank Function Syntax
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
order
 
by
 icd9
,
 ageatdx
)
Cross
Join
Rank Function Syntax
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
order
 
by
 icd9
,
 ageatdx
)
Cross
Join
Rank Function Syntax
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
order
 
by
 icd9
,
 ageatdx
)
Cross
Join
Rank Function Syntax
DROP
 
TABLE
 #Temp
USE
 AgeDx
SELECT
 ID
,
 icd9
,
 AgeAtDx 
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
 
order
 
by
 icd9
,
 AgeAtDx
)
 
AS
 [Repeated Dx]
INTO
 #Temp
FROM
 dbo
.
final 
WHERE
 ID
=
10
GROUP
 
BY
 ID
,
 icd9
,
 AgeAtDx 
Select
 
*
 
FROM
 #Temp 
ORDER
 
BY
 ID
,
 icd9
,
[Repeated Dx]
Use Database called AgeDx
Use table called Final
Cross
Join
Rank Function Syntax
DROP
 
TABLE
 #Temp
USE
 AgeDx
SELECT
 ID
,
 icd9
,
 AgeAtDx 
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
 
order
 
by
 icd9
,
 AgeAtDx
)
 
AS
 [Repeated Dx]
INTO
 #Temp
FROM
 dbo
.
final 
WHERE
 ID
=
10
GROUP
 
BY
 ID
,
 icd9
,
 AgeAtDx 
Select
 
*
 
FROM
 #Temp 
ORDER
 
BY
 ID
,
 icd9
,
[Repeated Dx]
For ease we are working with
person with ID 10
Cross
Join
Rank Function Syntax
DROP
 
TABLE
 #Temp
USE
 AgeDx
SELECT
 ID
,
 icd9
,
 AgeAtDx 
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
 
order
 
by
 icd9
,
 AgeAtDx
)
 
AS
 [Repeated Dx]
INTO
 #Temp
FROM
 dbo
.
final 
WHERE
 ID
=
10
GROUP
 
BY
 ID
,
 icd9
,
 AgeAtDx 
Select
 
*
 
FROM
 #Temp 
ORDER
 
BY
 ID
,
 icd9
,
[Repeated Dx]
Duplicates are removed so rank
and rank dense are same
Cross
Join
Rank Function Syntax
DROP
 
TABLE
 #Temp
USE
 AgeDx
SELECT
 ID
,
 icd9
,
 AgeAtDx 
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
 
order
 
by
 icd9
,
 AgeAtDx
)
 
AS
 [Repeated Dx]
INTO
 #Temp
FROM
 dbo
.
final 
WHERE
 ID
=
10
GROUP
 
BY
 ID
,
 icd9
,
 AgeAtDx 
Select
 
*
 
FROM
 #Temp 
ORDER
 
BY
 ID
,
 icd9
,
[Repeated Dx]
Order by Clause
Cross
Join
Rank Function Syntax
DROP
 
TABLE
 #Temp
USE
 AgeDx
SELECT
 ID
,
 icd9
,
 AgeAtDx 
,
 
Rank
()
 
OVER 
(
partition
 
by
 id
,
 icd9 
 
order
 
by
 icd9
,
 AgeAtDx
)
 
AS
 [Repeated Dx]
INTO
 #Temp
FROM
 dbo
.
final 
WHERE
 ID
=
10
GROUP
 
BY
 ID
,
 icd9
,
 AgeAtDx 
Select
 
*
 
FROM
 #Temp 
ORDER
 
BY
 ID
,
 icd9
,
[Repeated Dx]
Partition by clause
Cross
Join
Rank Function Syntax
ID
 
icd9
  
AgeAtDx
 
Repeated Dx
10
 
I041.89
 
64.666666
  
1
10
 
I112.0
  
64.25
   
1
10
 
I253.6
  
64.25
   
1
10
 
I253.6
  
64.75
   
2
10
 
I253.6
  
65.25
   
3
10
 
I263.9
  
64.916666
  
1
10
 
I272.4
  
64.25
   
1
10
 
I272.4
  
65.25
   
2
10
 
I275.2
  
64.916666
  
1
10
 
I275.2
  
65.583333
  
2
Cross
Join
Rank Function Syntax
ID
 
icd9
  
AgeAtDx
 
Repeated Dx
10
 
I041.89
 
64.666666
  
1
10
 
I112.0
  
64.25
   
1
10
 
I253.6
  
64.25
   
1
10
 
I253.6
  
64.75
   
2
10
 
I253.6
  
65.25
   
3
10
 
I263.9
  
64.916666
  
1
10
 
I272.4
  
64.25
   
1
10
 
I272.4
  
65.25
   
2
10
 
I275.2
  
64.916666
  
1
10
 
I275.2
  
65.583333
  
2
Cross
Join
Rank Function Syntax
ID
 
icd9
  
AgeAtDx
 
Repeated Dx
10
 
I041.89
 
64.666666
  
1
10
 
I112.0
  
64.25
   
1
10
 
I253.6
  
64.25
   
1
10
 
I253.6
  
64.75
   
2
10
 
I253.6
  
65.25
   
3
10
 
I263.9
  
64.916666
  
1
10
 
I272.4
  
64.25
   
1
10
 
I272.4
  
65.25
   
2
10
 
I275.2
  
64.916666
  
1
10
 
I275.2
  
65.583333
  
2
Cross
Join
Rank Function Syntax
ID
 
icd9
  
AgeAtDx
 
Repeated Dx
10
 
I041.89
 
64.666666
  
1
10
 
I112.0
  
64.25
   
1
10
 
I253.6
  
64.25
   
1
10
 
I253.6
  
64.75
   
2
10
 
I253.6
  
65.25
   
3
10
 
I263.9
  
64.916666
  
1
10
 
I272.4
  
64.25
   
1
10
 
I272.4
  
65.25
   
2
10
 
I275.2
  
64.916666
  
1
10
 
I275.2
  
65.583333
  
2
Cross
Join
Rank Function Syntax
ID
 
icd9
  
AgeAtDx
 
Repeated Dx
10
 
I041.89
 
64.666666
  
1
10
 
I112.0
  
64.25
   
1
10
 
I253.6
  
64.25
   
1
10
 
I253.6
  
64.75
   
2
10
 
I253.6
  
65.25
   
3
10
 
I263.9
  
64.916666
  
1
10
 
I272.4
  
64.25
   
1
10
 
I272.4
  
65.25
   
2
10
 
I275.2
  
64.916666
  
1
10
 
I275.2
  
65.583333
  
2
undefined
RANK ORDER FUNCTION IS USEFUL IN
ANALYSIS OF DATA IN ELECTRONIC
HEALTH RECORDS

								

								
								

										

												
Slide Note

												
In this section we discuss the rank order function using SQL.  This brief presentation was organized by Dr. Alemi.  

										

								


								
						

							
						


							

								

									
										
Embed
									
									
										
Share
									

									
									
									

								

								
							


							

							 
 							 
 							 
						  


							
							

								
Explore the intricacies of cross join rank functions in the Health Informatics Program at George Mason University. Delve into the process of ranking based on column values, handling repeated entries, and understanding rank skips and dense ranks. Gain valuable advice on optimizing data and dealing with patients having the same diagnosis at the same time.


								
    
																		
  •   Health Informatics
    • 
																	
  •    Cross Join
    • 
																	
  •    Rank Functions
    • 
																	
  •    GMU
    • 
																

								

								

									

										caer237
										

											 caer237 Follow
											

										

									

									
Uploaded on Sep 12, 2024 | 0 Views

								

								


								

									
Download Presentation

									
								

								
Please find below an Image/Link to download the presentation.

								
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
									Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.


									
									

									


										 
										 
										 
										 

									

									

									

									

										
 E N D 

									


								
Presentation Transcript

								

								
  1. Rank Order Function Farrokh Alemi, Ph.D. HEALTH INFORMATICS PROGRAM HI.GMU.EDU
    2. 
            
            
            
  2. Cross  Join Order Data Based  on a Column Values Repeated Same Dx  for Same Patient Rank HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    3. 
            
            
            
  3. Cross  Join Rank Skips Rank_Dense Rank &  Rank Dense Does Not HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    4. 
            
            
            
  4. Cross  Join Rank Skips:  1, 1, 3, 4 Rank Dense Does Not  Rank_Dense Rank &  HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    5. 
            
            
            
  5. Cross  Join Rank Skips:  1, 1, 3, 4 Rank Dense Does Not:  1, 1, 2, 3 Rank_Dense Rank &  HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    6. 
            
            
            
  6. Cross  Join ID 10 10 10 10 10 10 10 icd9 I276.1 I276.1 I276.1 I276.1 I276.1 I276.1 I276.1 Age  63.16 64.08 64.25 64.25 64.33 64.66 64.75 Rank 1 2 3 3 5 6 7 Skip 4 HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    7. 
            
            
            
  7. Advice: Cross  Join If it makes sense,  delete repeated  entries Rank Skips:  1, 1, 3, 4 Rank Dense Does Not:  1, 1, 2, 3 Rank_Dense Rank &  HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    8. 
            
            
            
  8. Advice: Patient having  same diagnosis at  same time Cross  Join Rank Skips:  3, 3, 5, 6 Rank Dense Does Not:  3, 3, 4, 5 Rank_Dense Rank &  HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    9. 
            
            
            
  9. Cross  Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    10. 
            
            
            
  10. Cross  Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    11. 
            
            
            
  11. Cross  Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    12. 
            
            
            
  12. Cross  Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    13. 
            
            
            
  13. Cross  Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    14. 
            
            
            
  14. Cross  Join Rank Function Syntax , Rank() OVER (partition by id, icd9 order by icd9, ageatdx) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    15. 
            
            
            
  15. Cross  Join Rank Function Syntax , Rank() OVER (partition by id, icd9 order by icd9, ageatdx) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    16. 
            
            
            
  16. Cross  Join Rank Function Syntax , Rank() OVER (partition by id, icd9 order by icd9, ageatdx) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    17. 
            
            
            
  17. DROP TABLE #Temp USE AgeDx SELECT ID, icd9, AgeAtDx  , Rank() OVER (partition by id, icd9  order by icd9, AgeAtDx) AS [Repeated Dx] INTO #Temp FROM dbo.final  WHERE ID=10 GROUP BY ID, icd9, AgeAtDx  Select * FROM #Temp ORDER BY ID, icd9,  [Repeated Dx] Cross  Join Use Database called AgeDx  Rank Function Syntax Use table called Final HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    18. 
            
            
            
  18. DROP TABLE #Temp USE AgeDx SELECT ID, icd9, AgeAtDx  , Rank() OVER (partition by id, icd9  order by icd9, AgeAtDx) AS [Repeated Dx] INTO #Temp FROM dbo.final  WHERE ID=10 GROUP BY ID, icd9, AgeAtDx  Select * FROM #Temp ORDER BY ID, icd9,  [Repeated Dx] Cross  Join Rank Function Syntax For ease we are working with  person with ID 10 HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    19. 
            
            
            
  19. DROP TABLE #Temp USE AgeDx SELECT ID, icd9, AgeAtDx  , Rank() OVER (partition by id, icd9  order by icd9, AgeAtDx) AS [Repeated Dx] INTO #Temp FROM dbo.final  WHERE ID=10 GROUP BY ID, icd9, AgeAtDx  Select * FROM #Temp ORDER BY ID, icd9,  [Repeated Dx] Cross  Join Rank Function Syntax Duplicates are removed so rank  and rank dense are same HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    20. 
            
            
            
  20. DROP TABLE #Temp USE AgeDx SELECT ID, icd9, AgeAtDx  , Rank() OVER (partition by id, icd9  order by icd9, AgeAtDx) AS [Repeated Dx] INTO #Temp FROM dbo.final  WHERE ID=10 GROUP BY ID, icd9, AgeAtDx  Select * FROM #Temp ORDER BY ID, icd9,  [Repeated Dx] Cross  Join Rank Function Syntax HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    21. 
            
            
            
  21. DROP TABLE #Temp USE AgeDx SELECT ID, icd9, AgeAtDx  , Rank() OVER (partition by id, icd9  order by icd9, AgeAtDx) AS [Repeated Dx] INTO #Temp FROM dbo.final  WHERE ID=10 GROUP BY ID, icd9, AgeAtDx  Select * FROM #Temp ORDER BY ID, icd9,  [Repeated Dx] Cross  Join Rank Function Syntax HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    22. 
            
            
            
  22. ID 10 10 10 10 10 10 10 10 10 10 icd9 I041.89 I112.0 I253.6 I253.6 I253.6 I263.9 I272.4 I272.4 I275.2 I275.2 AgeAtDx 64.666666 64.25 64.25 64.75 65.25 64.916666 64.25 65.25 64.916666 65.583333 Repeated Dx 1 1 1 2 3 1 1 2 1 2 Cross  Join Rank Function Syntax HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    23. 
            
            
            
  23. ID 10 10 10 10 10 10 10 10 10 10 icd9 I041.89 I112.0 I253.6 I253.6 I253.6 I263.9 I272.4 I272.4 I275.2 I275.2 AgeAtDx 64.666666 64.25 64.25 64.75 65.25 64.916666 64.25 65.25 64.916666 65.583333 Repeated Dx 1 1 1 2 3 1 1 2 1 2 Cross  Join Rank Function Syntax HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    24. 
            
            
            
  24. ID 10 10 10 10 10 10 10 10 10 10 icd9 I041.89 I112.0 I253.6 I253.6 I253.6 I263.9 I272.4 I272.4 I275.2 I275.2 AgeAtDx 64.666666 64.25 64.25 64.75 65.25 64.916666 64.25 65.25 64.916666 65.583333 Repeated Dx 1 1 1 2 3 1 1 2 1 2 Cross  Join Rank Function Syntax HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    25. 
            
            
            
  25. ID 10 10 10 10 10 10 10 10 10 10 icd9 I041.89 I112.0 I253.6 I253.6 I253.6 I263.9 I272.4 I272.4 I275.2 I275.2 AgeAtDx 64.666666 64.25 64.25 64.75 65.25 64.916666 64.25 65.25 64.916666 65.583333 Repeated Dx 1 1 1 2 3 1 1 2 1 2 Cross  Join Rank Function Syntax HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    26. 
            
            
            
  26. ID 10 10 10 10 10 10 10 10 10 10 icd9 I041.89 I112.0 I253.6 I253.6 I253.6 I263.9 I272.4 I272.4 I275.2 I275.2 AgeAtDx 64.666666 64.25 64.25 64.75 65.25 64.916666 64.25 65.25 64.916666 65.583333 Repeated Dx 1 1 1 2 3 1 1 2 1 2 Cross  Join Rank Function Syntax HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY
    27. 
            
            
            
  27. RANK ORDER FUNCTION IS USEFUL IN  ANALYSIS OF DATA IN ELECTRONIC  HEALTH RECORDS 
    28. 
            
            
            
							

							

					

					
					
					

						
Related

						

						


							
							
							
							

						

							

									

      

        
          

            

              Cross-Border Injury Claims in Canada_ Navigating Legal Complexities for Fair Compensation(20_03_2024)
            

            

              
Cross-Border Injury Claims in Canada_ Navigating Legal Complexities for Fair Compensation(20_03_2024)

              
Suresh

            

          

        
      


     

      

        
          

            

              Empowering Early Careerists: Navigating the Path to Success in Nursing Informatics Webinar
            

            

              
Empowering Early Careerists: Navigating the Path to Success in Nursing Informatics Webinar

              
maven

            

          

        
      


     

      

        
          

            

              CCPHIT Workforce Development Program in California
            

            

              
CCPHIT Workforce Development Program in California

              
delia

            

          

        
      


     

      

        
          

            

              Rank, Select, and Range in Binary Search Trees
            

            

              
Rank, Select, and Range in Binary Search Trees

              
alec

            

          

        
      


     

      

        
          

            

              Livestock Marketing Functions and Classification
            

            

              
Livestock Marketing Functions and Classification

              
sandy

            

          

        
      


     

      

        
          

            

              Improving Faculty Rank System at a Growing University
            

            

              
Improving Faculty Rank System at a Growing University

              
pierre

            

          

        
      


     

      

        
          

            

              SQL Functions for Database Queries
            

            

              
SQL Functions for Database Queries

              
kira

            

          

        
      


     

      

        
          

            

              Rank in Matrices
            

            

              
Rank in Matrices

              
bono_hly

            

          

        
      


     

      

        
          

            

              Rank and Nullity in Linear Algebra
            

            

              
Rank and Nullity in Linear Algebra

              
sal_fu

            

          

        
      


     

      

        
          

            

              Discussion on Rank Adaptation for SU-MIMO Transmission in IEEE 802.11-17/1253
            

            

              
Discussion on Rank Adaptation for SU-MIMO Transmission in IEEE 802.11-17/1253

              
yede771

            

          

        
      


     

      

        
          

            

              Low Threshold Rank Graphs and Their Structural Properties
            

            

              
Low Threshold Rank Graphs and Their Structural Properties

              
mcqueary_j

            

          

        
      


     

      

        
          

            

              P-Rank: A Comprehensive Structural Similarity Measure over Information Networks
            

            

              
P-Rank: A Comprehensive Structural Similarity Measure over Information Networks

              
exes507

            

          

        
      


     

      

        
          

            

              Learning-Based Low-Rank Approximations and Linear Sketches
            

            

              
Learning-Based Low-Rank Approximations and Linear Sketches

              
pjay

            

          

        
      


     

      

        
          

            

              Optimizing Join Enumeration in Transformation-based Query Optimizers
            

            

              
Optimizing Join Enumeration in Transformation-based Query Optimizers

              
vier784

            

          

        
      


     

      

        
          

            

              Join Algorithms in Database Systems
            

            

              
Join Algorithms in Database Systems

              
thei_813

            

          

        
      


     

      

        
          

            

              Innovative Biomedical Informatics Research by Dr. Gregory F. Cooper
            

            

              
Innovative Biomedical Informatics Research by Dr. Gregory F. Cooper

              
abdelaziz

            

          

        
      


     

      

        
          

            

              Red Cross Shelter Partnership Initiative in Missouri
            

            

              
Red Cross Shelter Partnership Initiative in Missouri

              
anoo_62

            

          

        
      


     

      

        
          

            

              Governors State University Master of Science in Health Informatics Program Overview
            

            

              
Governors State University Master of Science in Health Informatics Program Overview

              
tyel_2

            

          

        
      


     

      

        
          

            

              Control Flow Loops: While Loops in CS112 at GMU
            

            

              
Control Flow Loops: While Loops in CS112 at GMU

              
enyl

            

          

        
      


     

      

        
          

            

              Introduction to Clinical Informatics: Definition, Areas, and History
            

            

              
Introduction to Clinical Informatics: Definition, Areas, and History

              
audria

            

          

        
      


     

      

        
          

            

              Combinatorial Algorithms for Subset and Permutation Ranking
            

            

              
Combinatorial Algorithms for Subset and Permutation Ranking

              
jeri_704

            

          

        
      


     

      

        
          

            

              Tukey Control Chart in Health Informatics Program at GMU
            

            

              
Tukey Control Chart in Health Informatics Program at GMU

              
ski_g

            

          

        
      


     							

						

					

					

				


				

			

		
		

			

				
				

					
More Related Content

					

						

														

					

				

				
			

		












 





  
  
  



giItT1WQy@!-/#giItT1WQy@!-/#