wecc_data_migration/ampl_timepoints_script.sql at master · RAEL-Berkeley/wecc_data_migration · GitHub

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97


select * from training_sets where training_set_id = 1112;


select * from training_set_periods where training_set_id=1112;


SELECT
  timepoint_id,
  DATE_FORMAT(datetime_utc,'%Y%m%d%H') AS hour, period,
  DATE_FORMAT(datetime_utc,'%Y%m%d') AS date, hours_in_sample,
  MONTH(datetime_utc) AS month_of_year, HOUR(datetime_utc) as hour_of_day
FROM _training_set_timepoints JOIN study_timepoints  USING (timepoint_id)
WHERE training_set_id=1112
order by 1;


SELECT
  timepoint_id,
  datetime_utc, period,
  DATE_FORMAT(datetime_utc,'%Y%m%d') AS date, hours_in_sample,
  MONTH(datetime_utc) AS month_of_year, HOUR(datetime_utc) as hour_of_day
FROM _training_set_timepoints JOIN study_timepoints  USING (timepoint_id)
WHERE training_set_id=1112
order by 1;


select 	3 as study_timeframe_id,
        3 as time_sample_id,
        (case when w.period = 2016 then 5
            when w.period = 2026 then 6
            when w.period = 2036 then 7
            when w.period = 2046 then 8
            else -1 end) as period_id,
		date as name,
		4 as hours_per_tp,
		6 as num_timepoints,
		min(w.datetime_utc) as first_timepoint_utc,
		max(w.datetime_utc) as last_timepoint_utc,
        if(w.hours_in_sample < 1000, 365*10*2/(12.0*30), 365*10*28/(12.0*30)) as scaling_to_period,
        hours_in_sample
from  (SELECT
  timepoint_id,
  datetime_utc, period,
  DATE_FORMAT(datetime_utc,'%Y%m%d') AS date, hours_in_sample,
  MONTH(datetime_utc) AS month_of_year, HOUR(datetime_utc) as hour_of_day
FROM _training_set_timepoints JOIN study_timepoints  USING (timepoint_id)
WHERE training_set_id=1112
order by 1) as w
group by w.period, w.month_of_year, w.date, w.hours_in_sample;

-- -- example:
-- SELECT
--    (@cnt := @cnt + 1) AS rowNumber,
--    t.rowID
-- FROM myTable AS t
--  CROSS JOIN (SELECT @cnt := 0) AS dummy
-- WHERE t.CategoryID = 1
-- ORDER BY t.rowID ;

-- ampl_sampled_timeseries_from_AMPL.csv sent to db2
select 	(@cnt := @cnt + 1)  as sampled_timeseries_id,
		3 as study_timeframe_id,
        3 as time_sample_id,
        (case when w.period = 2016 then 5
            when w.period = 2026 then 6
            when w.period = 2036 then 7
            when w.period = 2046 then 8
            else -1 end) as period_id,
		concat(substring(date,1,4),'-', substring(date,5,2), '-', substring(date,7,2))as name,
		4 as hours_per_tp,
		6 as num_timepoints,
		min(w.datetime_utc) as first_timepoint_utc,
		max(w.datetime_utc) as last_timepoint_utc,
        if(w.hours_in_sample < 1000, 365*10*2/(12.0*30), 365*10*28/(12.0*30)) as scaling_to_period,
        hours_in_sample
from  (SELECT
  timepoint_id,
  datetime_utc, period,
  DATE_FORMAT(datetime_utc,'%Y%m%d') AS date, hours_in_sample,
  MONTH(datetime_utc) AS month_of_year, HOUR(datetime_utc) as hour_of_day
FROM _training_set_timepoints JOIN study_timepoints  USING (timepoint_id)
WHERE training_set_id=1112
order by 1) as w
CROSS JOIN (SELECT @cnt := 6) AS dummy
group by w.period, w.month_of_year, w.date, w.hours_in_sample;


-- mapping table
SELECT
  timepoint_id,
  datetime_utc
FROM _training_set_timepoints JOIN study_timepoints  USING (timepoint_id)
WHERE training_set_id=1112
order by 1;