unicorn/analyze_zips.sql

   1 -- Obviously you'll want to change 'm_pioneer', and if you want to port this to other ILSes,
   2 -- you'll want to change l_user_addr1_zip, l_user_city_state, l_user_addr1_county, etc.
   3
   4
   5 DROP TABLE IF EXISTS m_pioneer.zips;
   6
   7 CREATE TABLE m_pioneer.zips (
   8   zipcode TEXT,
   9   city_state TEXT,
  10   county TEXT,
  11   num INT
  12 );
  13
  14 CREATE OR REPLACE FUNCTION analyze_zips() RETURNS VOID AS $$
  15
  16 DECLARE
  17   zip TEXT;
  18
  19 BEGIN
  20
  21 FOR zip IN
  22   SELECT DISTINCT SUBSTRING(l_user_addr1_zip FROM 1 FOR 5)
  23   FROM m_pioneer.actor_usr_legacy
  24   WHERE l_user_addr1_zip <> ''
  25   LOOP
  26     INSERT INTO m_pioneer.zips (zipcode, city_state, county, num)
  27       SELECT zip, l_user_addr1_city_state, l_user_addr1_county, count(*)
  28         FROM m_pioneer.actor_usr_legacy
  29         WHERE l_user_addr1_zip=zip
  30         GROUP BY 1,2,3;
  31   END LOOP;
  32
  33 END;
  34
  35 $$ LANGUAGE plpgsql;
  36
  37 CREATE OR REPLACE FUNCTION report_zips() RETURNS SETOF m_pioneer.zips AS $$
  38
  39 DECLARE
  40   zip TEXT;
  41   output m_pioneer.zips%ROWTYPE;
  42
  43 BEGIN
  44
  45 FOR zip IN
  46   SELECT DISTINCT zipcode
  47     FROM m_pioneer.zips
  48   LOOP
  49 SELECT INTO OUTPUT *
  50   FROM m_pioneer.zips
  51   WHERE num = ( SELECT MAX(num) FROM m_pioneer.zips WHERE zipcode=zip ) AND zipcode=zip
  52   LIMIT 1;
  53   RETURN NEXT output;
  54 END LOOP;
  55
  56 END;
  57
  58 $$ LANGUAGE plpgsql;
  59
  60 SELECT analyze_zips();
  61
  62 SELECT * FROM report_zips() ORDER BY num DESC;
  63