154
edits
Changes
Jump to navigation
Jump to search
Patent Data Processing - SQL Steps (view source)
Revision as of 17:38, 14 June 2016
, 17:38, 14 June 2016no edit summary
** Assignees Data:
CREATE TABLE assignees_temp2 (
lastname varchar,
firstname varchar,
address varchar,
postcode varchar,
orgname varchar, --assignee varchar,
city varchar, --city2 varchar,
country varchar, --country2 varchar,
patentnumber int, --patent integer,
state varchar, --state2 varchar,
patentcountry varchar,
nationality2 varchar,
residence varchar,
asgseq int,
asgtype integer
);
CREATE TABLE assignees
(
lastname varchar,
firstname varchar,
address varchar,
postcode varchar,
orgname varchar,
city varchar,
country varchar,
patentnumber int,
state varchar,
patentcountry varchar,
nationality2 varchar,
residence varchar,
asgseq int,
asgtype integer
);
PatentData:
Column | Type | Modifiers
-------------+-------------------+-----------
patent | integer |
asgtype | integer |
assignee | character varying |
city | character varying |
state | character varying |
country | character varying |
nationality | character varying |
residence | character varying |
asgseq | integer |
(
patent int,
asgtype int,
assignee varchar ,
city varchar ,
state varchar ,
country varchar ,
nationality varchar ,
residence varchar ,
asgseq int
)
INSERT INTO assignees_merge
(
SELECT
'null',
'null',
'null',
'null',
a.assignee,
a.city,
a.country,
a.patent,
a.state,
'null',
a.nationality,
a.residence,
a.asgseq,
a.asgtype
FROM assignees a
);
/// Reference:
CREATE TABLE assignees_temp2 (
lastname varchar,
firstname varchar,
address varchar,
postcode varchar,
orgname varchar, --assignee varchar,
city varchar, --city2 varchar,
country varchar, --country2 varchar,
patentnumber int, --patent integer,
state varchar, --state2 varchar,
patentcountry varchar,
nationality2 varchar,
residence varchar,
asgseq int,
asgtype integer
);
//////
Patent_2015:
Column | Type | Modifiers
---------------+---------+-----------
lastname | text |
firstname | text |
orgname | text |
city | text |
country | text |
patentcountry | text |
patentnumber | integer |
state | text |
address | text |
postcode | text |
(
lastname varchar,
firstname varchar,
orgname varchar,
city varchar,
country varchar,
patentcountry varchar,
patentnumber int,
state varchar,
address varchar,
postcode varchar
)
INSERT INTO assignees_merge
(
SELECT
assignees.lastname,
assignees.firstname,
assignees.address,
assignees.postcode,
assignees.orgname,
assignees.city,
assignees.country,
assignees.patentnumber,
assignees.state,
assignees.patentcountry,
'null',
'null',
-1,
-1
FROM assignees
);
\COPY assignees_merge TO '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
\COPY assignees FROM '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
--1607724
\COPY assignees_merge TO '/tmp/assignees_merge_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
\COPY assignees FROM '/tmp/assignees_merge_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
--3818842
SELECT count(*), *
from assignees
GROUP BY lastname, firstname, address, postcode, orgname, city, country, patentnumber, state, patentcountry, nationality2, residence, asgseq, asgtype
HAVING COUNT(*) > 1
;
CREATE TABLE Assignees2 AS
SELECT *
from assignees
GROUP BY lastname, firstname, address, postcode, orgname, city, country, patentnumber, state, patentcountry, nationality2, residence, asgseq, asgtype
HAVING COUNT(*) = 1
;
DROP TABLE Assignees;
ALTER TABLE Assignees2 RENAME TO Assignees;
--
Patentdata:
