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

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 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.

Presentation Transcript

1. Rank Order Function Farrokh Alemi, Ph.D. HEALTH INFORMATICS PROGRAM HI.GMU.EDU

2. Cross Join Order Data Based on a Column Values Repeated Same Dx for Same Patient Rank HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

3. Cross Join Rank Skips Rank_Dense Rank & Rank Dense Does Not HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

4. Cross Join Rank Skips: 1, 1, 3, 4 Rank Dense Does Not Rank_Dense Rank & HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

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. 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. 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. 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. Cross Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

10. Cross Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

11. Cross Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

12. Cross Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

13. Cross Join Rank Function Syntax RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

14. Cross Join Rank Function Syntax , Rank() OVER (partition by id, icd9 order by icd9, ageatdx) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

15. Cross Join Rank Function Syntax , Rank() OVER (partition by id, icd9 order by icd9, ageatdx) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

16. Cross Join Rank Function Syntax , Rank() OVER (partition by id, icd9 order by icd9, ageatdx) HEALTH INFORMATICS PROGRAM GEORGE MASON UNIVERSITY

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. 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. 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. 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. 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. 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. 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. 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. 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. 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. RANK ORDER FUNCTION IS USEFUL IN ANALYSIS OF DATA IN ELECTRONIC HEALTH RECORDS