First working draft of analyze_zips.sql

[migration-tools.git] / unicorn / analyze_zips.sql

diff --git a/unicorn/analyze_zips.sql b/unicorn/analyze_zips.sql
new file mode 100644 (file)
index 0000000..1aed190
--- /dev/null
+++ b/unicorn/analyze_zips.sql
@@ -0,0 +1,63 @@
+-- Obviously you'll want to change 'm_pioneer', and if you want to port this to other ILSes,
+-- you'll want to change l_user_addr1_zip, l_user_city_state, l_user_addr1_county, etc.
+
+
+DROP TABLE IF EXISTS m_pioneer.zips;
+
+CREATE TABLE m_pioneer.zips (
+  zipcode TEXT,
+  city_state TEXT,
+  county TEXT,
+  num INT
+);
+
+CREATE OR REPLACE FUNCTION analyze_zips() RETURNS VOID AS $$
+
+DECLARE
+  zip TEXT;
+  
+BEGIN
+
+FOR zip IN 
+  SELECT DISTINCT SUBSTRING(l_user_addr1_zip FROM 1 FOR 5)
+  FROM m_pioneer.actor_usr_legacy
+  WHERE l_user_addr1_zip <> ''
+  LOOP
+    INSERT INTO m_pioneer.zips (zipcode, city_state, county, num) 
+      SELECT zip, l_user_addr1_city_state, l_user_addr1_county, count(*)
+        FROM m_pioneer.actor_usr_legacy 
+        WHERE l_user_addr1_zip=zip 
+        GROUP BY 1,2,3;
+  END LOOP;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION report_zips() RETURNS SETOF m_pioneer.zips AS $$
+
+DECLARE
+  zip TEXT;
+  output m_pioneer.zips%ROWTYPE;  
+
+BEGIN
+
+FOR zip IN 
+  SELECT DISTINCT zipcode
+    FROM m_pioneer.zips
+  LOOP
+SELECT INTO OUTPUT *
+  FROM m_pioneer.zips
+  WHERE num = (        SELECT MAX(num) FROM m_pioneer.zips WHERE zipcode=zip ) AND zipcode=zip
+  LIMIT 1;
+  RETURN NEXT output;
+END LOOP;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+SELECT analyze_zips();
+
+SELECT * FROM report_zips() ORDER BY num DESC;
+