Last active
April 25, 2024 01:12
-
-
Save MarioZZJ/a8afdc5284106a9f128d5ad3924e73da to your computer and use it in GitHub Desktop.
计算种子文献前后1年内含同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
| DECLARE @PMID INT; | |
| DECLARE @PYEAR INT; | |
| DECLARE @THRESHOLD INT = 2; | |
| DECLARE cur CURSOR FOR | |
| SELECT py.pmid AS PMID, py.publish_year AS PYEAR FROM | |
| userdb_mariozzj_SAO4D.dbo.sample_xyc AS s | |
| INNER JOIN | |
| pubmed_2024.dbo.pmid_py AS py | |
| ON s.pmid = py.pmid; | |
| DECLARE @MergeResult TABLE ( | |
| seed INT, | |
| pmid INT, | |
| num_comesh INT, | |
| num_cocite INT | |
| ); | |
| OPEN cur; | |
| FETCH NEXT FROM cur INTO @PMID, @PYEAR; | |
| WHILE @@FETCH_STATUS = 0 | |
| BEGIN | |
| WITH pym AS ( | |
| SELECT py.pmid, py.publish_year, pm.descriptor_ui_n FROM | |
| ( | |
| SELECT pmid, publish_year FROM pubmed_2024.dbo.pmid_py | |
| ) AS py | |
| INNER JOIN | |
| ( | |
| SELECT pmid, descriptor_ui_n FROM pubmed_2024.dbo.pub_descriptor | |
| ) AS pm | |
| ON py.pmid = pm.pmid | |
| ), comesh AS ( | |
| SELECT pmid, COUNT(*) AS num_comesh FROM pym | |
| WHERE publish_year >= @PYEAR -1 AND publish_year <= @PYEAR +1 AND descriptor_ui_n IN | |
| ( | |
| SELECT descriptor_ui_n FROM pubmed_2024.dbo.pub_descriptor | |
| WHERE pmid = @PMID | |
| ) | |
| GROUP BY pmid | |
| HAVING COUNT(*) >= @THRESHOLD | |
| ), fp_ref AS ( | |
| SELECT referenced FROM pubmed_2024.dbo.open_citation_collection | |
| WHERE citing = @PMID | |
| ) | |
| INSERT INTO @MergeResult (seed, pmid, num_comesh, num_cocite) | |
| SELECT @PMID AS seed, comesh.pmid, comesh.num_comesh, COALESCE(cocite.num_cocite,0) AS num_cocite FROM | |
| comesh LEFT JOIN | |
| ( | |
| SELECT comesh_ref.pmid, COUNT(*) AS num_cocite FROM | |
| ( | |
| SELECT comesh.pmid, occ.referenced | |
| FROM comesh | |
| INNER JOIN pubmed_2024.dbo.open_citation_collection AS occ | |
| ON comesh.pmid = occ.citing | |
| ) AS comesh_ref | |
| INNER JOIN fp_ref | |
| ON comesh_ref.referenced = fp_ref.referenced | |
| GROUP BY comesh_ref.pmid | |
| ) AS cocite | |
| ON comesh.pmid = cocite.pmid | |
| ORDER BY num_cocite | |
| FETCH NEXT FROM cur INTO @PMID, @PYEAR; | |
| END | |
| CLOSE cur; | |
| DEALLOCATE cur; | |
| SELECT * | |
| FROM @MergeResult; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment