Last active
May 6, 2024 14:02
-
-
Save MarioZZJ/003277d5e401a0803df567e5c1b222b3 to your computer and use it in GitHub Desktop.
获取三代引文边、发表年、mesh词
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH g1 As ( | |
| SELECT citing, referenced, 1 As gen | |
| FROM userdb_mariozzj_SAO4D.dbo.sample_xyc AS s INNER JOIN pubmed_2024.dbo.open_citation_collection As occ | |
| ON s.pmid = occ.citing | |
| ), g2 AS ( | |
| SELECT citing, referenced, 2 AS gen | |
| FROM | |
| (SELECT referenced AS r1 FROM g1 GROUP BY referenced) AS rg1 INNER JOIN pubmed_2024.dbo.open_citation_collection As occ | |
| ON rg1.r1 = occ.citing | |
| ), g3 AS ( | |
| SELECT citing, referenced , 3 AS gen | |
| FROM | |
| (SELECT referenced AS r2 FROM g2 GROUP BY referenced) AS rg2 INNER JOIN pubmed_2024.dbo.open_citation_collection As occ | |
| ON rg2.r2 = occ.citing | |
| ) | |
| WITH g1 As ( | |
| SELECT citing, referenced, 1 As gen | |
| FROM userdb_mariozzj_SAO4D.dbo.sample_xyc AS s INNER JOIN pubmed_2024.dbo.open_citation_collection As occ | |
| ON s.pmid = occ.citing | |
| ), g2 AS ( | |
| SELECT citing, referenced, 2 AS gen | |
| FROM | |
| (SELECT referenced AS r1 FROM g1 GROUP BY referenced) AS rg1 INNER JOIN pubmed_2024.dbo.open_citation_collection As occ | |
| ON rg1.r1 = occ.citing | |
| ), g3 AS ( | |
| SELECT citing, referenced , 3 AS gen | |
| FROM | |
| (SELECT referenced AS r2 FROM g2 GROUP BY referenced) AS rg2 INNER JOIN pubmed_2024.dbo.open_citation_collection As occ | |
| ON rg2.r2 = occ.citing | |
| ), papers AS ( | |
| SELECT referenced FROM g1 GROUP BY referenced | |
| UNION | |
| SELECT referenced FROM g2 GROUP BY referenced | |
| UNION | |
| SELECT referenced FROM g3 GROUP BY referenced | |
| ) | |
| -- 获取三代引文边 | |
| -- SELECT * FROM g1 | |
| -- UNION | |
| -- SELECT * FROM g2 | |
| -- UNION | |
| -- SELECT * FROM g3 | |
| -- 获取三代引文发表年 | |
| -- SELECT pmid, publish_year FROM | |
| -- papers LEFT JOIN pubmed_2024.dbo.pmid_py AS py | |
| -- ON papers.referenced = py.pmid | |
| -- 获取三代引文mesh | |
| -- SELECT pmid, descriptor_ui AS mesh FROM | |
| -- ( | |
| -- SELECT pmid, descriptor_ui_n FROM | |
| -- papers INNER JOIN pubmed_2024.dbo.pub_descriptor AS pd | |
| -- ON papers.referenced = pd.pmid | |
| -- ) AS pu | |
| -- INNER JOIN pubmed_2024.dbo.ui_descriptor AS ud | |
| -- ON pu.descriptor_ui_n = ud.descriptor_ui_n |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment