154
edits
Changes
Jump to navigation
Jump to search
'''Patent_2015 Schema:'''
Patent Data Processing - SQL Steps (view source)
Revision as of 17:02, 23 June 2016
, 17:02, 23 June 2016→Patents
Column | Type | Modifiers
patent,
kind,
gdate, 'NULL', 'NULL', NULL, NULL, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', -1, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', claims, apptype, appnum, gyear, appdate, appyear FROM patents ); -- RESULT : INSERT 0 3984771
patent_2015:
COPY SCRIPTS:
patentdata:
\COPY patents_merged TO '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 3984771
patent_2015:
\COPY patents_merged TO '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; --COPY 1646225
PATENTS TABLE
\COPY patents FROM '/tmp/merged_patents_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 3984771 \COPY patents FROM '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 1646225
====TESTING ====
select count(*) FROM (SELECT DISTINCT patentnumber FROM patents) AS t; --RESULT: 5411151 EXPECTED: 5426566
We found some copies of a few rows, where both the patent_2015 and patentdata
SELECT COUNT(*), * FROM patents GROUP BY patentnumber,
kind,
grantdate,
appdate,
appyear
HAVING COUNT(*) > 1;
SELECT patentnumber, count(*) FROM patents GROUP BY patentnumber HAVING count(*)>1; --7640598
SELECT * FROM patents op WHERE op.patentnumber IN
(
SELECT ip.patentnumber
HAVING COUNT(*)>1
)
ORDER BY op.patentnumber;
( SELECT * INTO patentsCleaned FROM patents op WHERE op.patentnumber IN
(
SELECT ip.patentnumber
HAVING COUNT(*)=1
)
ORDER BY op.patentnumber ) --SELECT 5191306
INSERT INTO patentsCleaned( SELECT * FROM patents op WHERE op.patentnumber IN
(
SELECT ip.patentnumber
HAVING COUNT(*)>1
)
AND op.applicationnumber NOT LIKE 'NULL' ORDER BY op.patentnumber );
--219845
====TESTING:==== allpatent=# select count(*) from patentsCleaned; count --------- 5411151 (1 row)
allpatent=# select count(*), patentnumber FROM patentsCleaned group by patentnumber having count(*) > 1; count | patentnumber -------+-------------- (0 rows)
== Citations==
