· 5 years ago · Mar 26, 2020, 04:48 PM
1\set table_name trinity.household
2
3DROP TABLE IF EXISTS :table_name CASCADE;
4
5CREATE TEMPORARY TABLE temp1 AS
6(
7SELECT
8 ign.id
9 ,ign.household as ign_household
10 ,ign.is_habitable
11 ,opt_oi.id AS optimum_oi_id
12 ,opth_oi.household AS optimum_oi_household
13 ,opth_oi.household_private AS optimum_oi_household_private
14 ,opth_oi.household_professional AS optimum_oi_household_professional
15 ,opt_rip.id AS optimum_rip_id
16 ,opth_rip.household AS optimum_rip_household
17 ,opth_rip.household_private AS optimum_rip_household_private
18 ,opth_rip.household_professional AS optimum_rip_household_professional
19 ,oras.id AS oras_id
20 ,orash.household AS oras_household
21 ,NULL::int AS oras_household_private
22 ,NULL::int AS oras_household_professional
23 ,med.hexakey AS mediapost_hk
24 ,medh.household AS mediapost_household
25 ,NULL::int AS mediapost_household_private
26 ,NULL::int AS mediapost_household_professional
27 ,pb.hexakey AS pitney_bowes_hk
28 ,pbh.household AS pitney_bowes_household
29 ,pbh.household_private AS pitney_bowes_household_private
30 ,pbh.household_professional AS pitney_bowes_household_professional
31 ,ipe.id AS ipe_id
32 ,ipeh.household AS ipe_household
33 ,NULL::int AS ipe_household_private
34 ,NULL::int AS ipe_household_professional
35FROM ign.master_address AS ign
36LEFT JOIN oras.match_ign AS oras --from here get the matches
37 ON ign.id = oras.ign_id
38LEFT JOIN optimum_oi.match_ign AS opt_oi
39 ON ign.id = opt_oi.ign_id
40LEFT JOIN optimum_rip.match_ign AS opt_rip
41 ON ign.id = opt_rip.ign_id
42LEFT JOIN mediapost.match_ign AS med
43 ON ign.id = med.ign_id
44LEFT JOIN pitney_bowes.match_ign AS pb
45 ON ign.id = pb.ign_id
46LEFT JOIN ipe.match_ign AS ipe
47 ON ign.id = ipe.ign_id
48LEFT JOIN oras.master_address AS orash-- from here get household
49 ON oras.id = orash.id
50LEFT JOIN optimum_oi.address AS opth_oi
51 ON opt_oi.id = opth_oi.id
52LEFT JOIN optimum_rip.address AS opth_rip
53 ON opt_rip.id = opth_rip.id
54LEFT JOIN mediapost.master_address AS medh
55 ON med.hexakey = medh.hexakey
56LEFT JOIN pitney_bowes.master_address AS pbh
57 ON pb.hexakey = pbh.hexakey
58LEFT JOIN ipe.master_address AS ipeh
59 ON ipe.id = ipeh.id
60);
61
62
63CREATE TEMPORARY TABLE matching_score AS
64(
65SELECT *,
66 (optimum_oi_id IS NOT NULL AND optimum_oi_household IS NOT NULL AND optimum_oi_household > 0)::int
67 + (optimum_rip_id IS NOT NULL AND optimum_rip_household IS NOT NULL AND optimum_rip_household > 0)::int
68 + (oras_id IS NOT NULL)::int
69 + (mediapost_hk IS NOT NULL AND mediapost_household IS NOT NULL)::int
70 + (pitney_bowes_hk IS NOT NULL AND pitney_bowes_household > 0)::int
71 + (ipe_id IS NOT NULL AND ipe_household > 0)::int AS matching_score
72FROM temp1
73);
74
75CREATE TABLE :table_name AS
76(
77SELECT
78 *,
79 CASE
80 WHEN ign_household IS NOT NULL AND ign_household <> 0 THEN ign_household
81 -- WHEN optimum_id IS NOT NULL AND optimum_household > 0 THEN optimum_household
82 ELSE
83 CASE
84 WHEN matching_score > 0 THEN
85 (
86 COALESCE(optimum_oi_household, 0)
87 + COALESCE(optimum_rip_household, 0)
88 + COALESCE(oras_household, 0)
89 + COALESCE(mediapost_household, 0)
90 + COALESCE(pitney_bowes_household, 0)
91 + COALESCE(ipe_household, 0)
92 )::FLOAT / matching_score
93 WHEN matching_score = 0 AND ign_household = 0 THEN 0
94 WHEN matching_score = 0 AND is_habitable = 0 THEN 0
95 WHEN matching_score = 0 AND pitney_bowes_household = 0 THEN 1
96 WHEN matching_score = 0 AND is_habitable = 1 THEN 1
97 ELSE 1
98 END
99 END AS household_mean,
100 CASE
101 WHEN ign_household IS NOT NULL THEN ign_household
102 WHEN matching_score > 0 THEN
103 (
104 COALESCE(optimum_oi_household_private, 0)
105 + COALESCE(optimum_rip_household_private, 0)
106 + COALESCE(oras_household_private, 0)
107 + COALESCE(mediapost_household_private, 0)
108 + COALESCE(pitney_bowes_household_private, 0)
109 + COALESCE(ipe_household_private, 0)
110 )::FLOAT / matching_score
111 ELSE NULL
112 END AS household_private_mean,
113 CASE
114 WHEN matching_score > 0 THEN
115 (
116 COALESCE(optimum_oi_household_professional, 0)
117 + COALESCE(optimum_rip_household_professional, 0)
118 + COALESCE(oras_household_professional, 0)
119 + COALESCE(mediapost_household_professional, 0)
120 + COALESCE(pitney_bowes_household_professional, 0)
121 + COALESCE(ipe_household_professional, 0)
122 )::FLOAT / matching_score
123 ELSE NULL
124 END AS household_professional_mean,
125 CASE
126 WHEN ign_household IS NOT NULL AND ign_household <> 0 THEN 'ign'
127 -- WHEN optimum_id IS NOT NULL AND optimum_household > 0 THEN 'optimum'
128 ELSE
129 CASE
130 WHEN matching_score > 0 THEN CONCAT('mean_of_', matching_score, '_sources')
131 WHEN matching_score = 0 AND is_habitable = 0 THEN 'ign_not_habitable'
132 WHEN matching_score = 0 AND ign_household = 0 THEN 'ign'
133 WHEN matching_score = 0 AND pitney_bowes_household = 0 THEN 'default_pbs'
134 WHEN matching_score = 0 AND is_habitable = 1 THEN 'ign_habitable'
135 ELSE 'default'
136 END
137 END AS origin
138FROM matching_score
139);
140
141-- in case there are still some 0s left (cf #314)
142UPDATE :table_name
143SET (household_mean, household_private_mean, household_professional_mean, origin, matching_score) = (1, NULL, NULL, 'default', 0)
144WHERE household_mean = 0 AND matching_score > 0;
145
146ALTER TABLE :table_name ADD PRIMARY KEY (id);