-
Notifications
You must be signed in to change notification settings - Fork 1
/
recall_adjustment_pre.sql.tmpl
45 lines (37 loc) · 1.47 KB
/
recall_adjustment_pre.sql.tmpl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SET ROLE {{pg_role}};
CREATE SCHEMA IF NOT EXISTS {{schema}};
-- ASSUMPTIONS:
-- 1. every model for a given train_end_time uses the same cohort
DROP TABLE IF EXISTS tmp_bias_end_times;
CREATE LOCAL TEMPORARY TABLE tmp_bias_end_times
ON COMMIT PRESERVE ROWS
AS
SELECT UNNEST(ARRAY['{{date_pairs|map('first')|join("','")}}'])::TIMESTAMP AS past_train_end_time,
UNNEST(ARRAY['{{date_pairs|map('last')|join("','")}}'])::TIMESTAMP AS future_train_end_time
;
DROP TABLE IF EXISTS tmp_bias_list_sizes;
CREATE LOCAL TEMPORARY TABLE tmp_bias_list_sizes
ON COMMIT PRESERVE ROWS
AS
SELECT UNNEST(ARRAY[{{list_sizes|join(',')}}])::INT AS list_size
;
DROP TABLE IF EXISTS tmp_bias_models;
CREATE LOCAL TEMPORARY TABLE tmp_bias_models
ON COMMIT PRESERVE ROWS
AS
WITH all_end_times AS (
SELECT DISTINCT past_train_end_time AS train_end_time FROM tmp_bias_end_times
UNION DISTINCT
SELECT DISTINCT future_train_end_time AS train_end_time FROM tmp_bias_end_times
)
SELECT DISTINCT m.model_id, m.model_group_id, m.train_end_time
FROM model_metadata.experiment_models em
JOIN model_metadata.models m USING(model_hash)
JOIN model_metadata.model_groups mg USING(model_group_id)
JOIN all_end_times USING(train_end_time)
WHERE em.experiment_hash IN ('{{experiment_hashes|join("','")}}')
{% if exclude_model_groups %}
AND mg.model_group_id NOT IN ({{exclude_model_groups|join(",")}})
{% endif %}
;
ALTER TABLE tmp_bias_models ADD PRIMARY KEY (model_id);