search

JetBrains-Research / pubtrends

Scientific literature explorer. Runs a Pubmed or Semantic Scholar search and allows user to explore high-level structure of result papers
Apache License 2.0
36 stars 2 forks source link

Make SSPublications table store unique papers only #316

Closed olegs closed 2 years ago

olegs commented 2 years ago

Query to be executed:

DELETE FROM SSPublications
WHERE (crc32id, ssid) IN (SELECT crc32id, ssid FROM (SELECT crc32id, ssid, ROW_NUMBER() OVER( PARTITION BY crc32id, ssid ) AS row_num FROM SSPublications) t WHERE t.row_num > 1);
olegs commented 2 years ago

Example of select query:

SELECT crc32id, ssid, title FROM SSPublications
WHERE (crc32id, ssid) IN (SELECT crc32id, ssid FROM (SELECT crc32id, ssid, ROW_NUMBER() OVER( PARTITION BY crc32id, ssid ) AS row_num FROM SSPublications) t WHERE t.row_num > 1 LIMIT 10);
   crc32id   |                   ssid                   |                                                                         title                                                                          
-------------+------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------
 -2126627875 | e167a0624fc9d8780346116504cd4da22b806ce4 | Illustrations
 -2126627875 | e167a0624fc9d8780346116504cd4da22b806ce4 | Illustrations
 -2146261793 | 66f2537f609f875b7b0650411404ffb0a6b647b6 | The Accuracy and Reliability of Plantar PressureMeasurements for the Early Diagnosis ofFoot Deformities in Patients Suffering fromRheumatoid Arthritis
 -2146261793 | 66f2537f609f875b7b0650411404ffb0a6b647b6 | The Accuracy and Reliability of Plantar PressureMeasurements for the Early Diagnosis ofFoot Deformities in Patients Suffering fromRheumatoid Arthritis
 -2140927985 | 0266dcd5305099204476da4ad2052ca86562d941 | High Body Adiposity Drives Glucose Intolerance and Increases Cardiovascular Risk in Normoglycemic Subjects
 -2140927985 | 0266dcd5305099204476da4ad2052ca86562d941 | High Body Adiposity Drives Glucose Intolerance and Increases Cardiovascular Risk in Normoglycemic Subjects
 -2132866605 | dcb907aaed5063cf115793519e15c3b8ce01d1bc | Telemedicine in ultrasound: new solutions.
 -2132866605 | dcb907aaed5063cf115793519e15c3b8ce01d1bc | Telemedicine in ultrasound: new solutions.
 -2134391775 | 1e134162deed10aaf637b57ac11a6f227f7b8f48 | A novel waveguide structure to reduce beam divergence and threshold current in GaInP/AlGaInP visible quantum-well lasers
 -2134391775 | 1e134162deed10aaf637b57ac11a6f227f7b8f48 | A novel waveguide structure to reduce beam divergence and threshold current in GaInP/AlGaInP visible quantum-well lasers
 -2128534122 | fdc9a9d123bc1d07f6a8d26749b198dd93e87bb0 | Accessing the medical devices market in Egypt and Saudi Arabia: a systematic review of policies and regulations
 -2128534122 | fdc9a9d123bc1d07f6a8d26749b198dd93e87bb0 | Accessing the medical devices market in Egypt and Saudi Arabia: a systematic review of policies and regulations
 -2125794275 | d590cbc9143ba95fa6b9fa6f3aceca31473c1a55 | Study and improvement on equivalence classes of PostgreSQL query optimization
 -2125794275 | d590cbc9143ba95fa6b9fa6f3aceca31473c1a55 | Study and improvement on equivalence classes of PostgreSQL query optimization
 -2143329563 | b02fd1f91be9232cddd1c9c30c8185ba13936820 | Protect ya grandma! The effects of students’ epistemic beliefs and prosocial values on COVID-19 vaccination intentions
 -2143329563 | b02fd1f91be9232cddd1c9c30c8185ba13936820 | Protect ya grandma! The effects of students’ epistemic beliefs and prosocial values on COVID-19 vaccination intentions
 -2122928331 | 1c8a1c174c2d7cb0db3128bf87a092b8905db636 | LibGuides: Psychology: Primary Sources
 -2122928331 | 1c8a1c174c2d7cb0db3128bf87a092b8905db636 | LibGuides: Psychology: Primary Sources
 -2125805662 | d1aa679b7d0414beeedbab307ac92a5aa1217885 | [오늘의 시와 시인] 발
 -2125805662 | d1aa679b7d0414beeedbab307ac92a5aa1217885 | [오늘의 시와 시인] 발
(20 rows)