220
edits
Changes
Jump to navigation
Jump to search
→Cleaning mas table
masnodupes should have no duplicates based on targetname, targetstate, announceddate. After running it thru the matcher you will get about 800 matching errors. We decided to exclude any rows that had equivalent targetname, targetstate where the announced dates fell within the same week. In this case we took the minimum date value and excluded the other row. Below are the queries to make those tables.
DROP TABLE masdistinctkeys;
CREATE TABLE masdistinctkeys AS
SELECT DISTINCT targetname, targetstate, announceddate
FROM masnodupes;
--114825
\COPY masdistinctkeys TO 'masdistinctkeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
DROP TABLE masdistinctkeysmindates;
CREATE TABLE masdistinctkeysmindates AS
SELECT targetname, targetstate, MIN(announceddate) AS announceddate
FROM masdistinctkeys
GROUP BY targetname, targetstate;
--113236
DROP TABLE masdistinctkeysdatewindow;
CREATE TABLE masdistinctkeysdatewindow AS
SELECT masdistinctkeys.*, masdistinctkeysmindates.announceddate as minanndate,
CASE WHEN masdistinctkeys.announceddate - INTERVAL '7 day' > masdistinctkeysmindates.announceddate THEN 1::int
ELSE 0::int
END AS dateflag
FROM masdistinctkeys JOIN masdistinctkeysmindates ON (masdistinctkeys.targetname = masdistinctkeysmindates.targetname AND
masdistinctkeys.targetstate = masdistinctkeysmindates.targetstate);
--114825
\COPY masdistinctkeysdatewindow TO 'masdistinctkeysdatewindow.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
CREATE TABLE masdistinctkeys1 AS
SELECT targetname, targetstate, announceddate
FROM masdistinctkeysdatewindow
WHERE dateflag = 0;
--113267
\COPY masdistinctkeys1 TO 'masdistinctkeys1.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
