Work on eyedro bucket analysis

JCKalt commented 3 months ago

[x] add tz to weather_data table, DDL, back fetching and daily fetching update
[x] fix missing weather hours (see comment)
[x] sql-to-excel merge spreadsheet
[x] create detail version of temp_buckets_by_interval.sql - new and refactor original view (see comment)
[x] create Dev-Temp-Avg-Rdngs-by-Interval
[x] Change Dev-Rdngs-wTemp-by-Intv to not join using start_of_interval but local hour, and include temp not avg_temp
[x] Sort Dev-Rdngs-wTemp-By-Time BY TIME
[x] Create Dev-Rdngs-wTemp-by-Bucket
[x] Create Dev-Pct-Savings-By-Bucket
[x] reverse order of tabs
[x] add wh_enabled, wh_disabled to Dev-Rdings-wTemp-By-Bucket
[x] Research Timezones for dg 16 what is it today. Check after Christi resets
[x] grab rest of 1 hour times sheets
[x] do 69 Vixo 8am-8pm test start 11/29/23 8:30am
[x] try 24 hour intervals
[x] send Christi latest display groups detail csv inquire as to agreement
[x] pull down new readings
[x] check with Christi's spreadsheets
[x] make plan for adding Coolnmoics data as well as new schedule filters -- where to apply these and how?
[ ] check visual crossing for hour fraction data
[ ] Explain missing readings for Temp Bucket 103 on dg16 spreadsheet (see below)
[ ] Explain ENTIRELY missing 103 bucket for modified grp pct savings (see below)
[ ] Christi's TODO List (see below)
[ ] Chrsiti's TODO List2 (see below)
[ ] Chrsiti's TODO List3 (see below)
[ ] Add new groups one at a time and rebuild 5327,5328,5329 and 8635

JCKalt commented 3 months ago

[ ] add tz to weather_data table, DDL, back fetching and daily fetching update

JCKalt commented 3 months ago

missing weather hours: This query below only returns one hour per day.

select date,id,hour from weather_data inner join hourly_data using (id) where address = 'Las Vegas, NV' and date >= '2024-06-01' order by date

select date,id,hour
from weather_data inner join hourly_data
  using (id)
where address = 'Las Vegas, NV' 
  and date >= '2024-06-01' order by date

;
    date    |  id   |        hour         
------------+-------+---------------------
 2024-06-01 | 11914 | 2023-06-19 14:00:00
 2024-06-02 | 11921 | 2023-06-19 21:00:00
 2024-06-03 | 11928 | 2024-01-24 04:00:00
 2024-06-04 | 11935 | 2024-01-24 11:00:00
 2024-06-05 | 11942 | 2024-01-24 18:00:00
 2024-06-06 | 11949 | 2024-04-01 01:00:00
 2024-06-07 | 11956 | 2024-04-01 08:00:00
 2024-06-08 | 11963 | 2024-04-01 15:00:00
 2024-06-09 | 11970 | 2024-04-01 22:00:00
 2024-06-10 | 11977 | 2023-10-29 05:00:00
(10 rows)

eyedro_2=>

JCKalt commented 3 months ago

This provides temperature buckets:

cat temp_buckets_by_interval.sql 
SELECT
    tz,
    address,
    start_of_interval,
    temp_bucket,
    avg_temp
FROM temperature_3_hour_intervals(16)
ORDER BY
    tz,
    address,
    start_of_interval,
    temp_bucket,
    avg_temp;

the temperature_3_hour_intervals FUNCTION has a sub query that I want to break into a separate view to provide a detail level view of this data for analysis.

JCKalt commented 2 months ago

Christi found issue with Temp bucket 103 on dg16-grp-pct-savings-3hour-intervals-coolnomix-filtered.xlsx Main sheet shows count enabled 5 and count disabled 3 but Dev-Rdngs-3hr-wTemp-By-3hr bucket shows only 1 reading.


python src/sql-to-excel.py --input-files audit-3hour-for-group-coolnomix.json
cp output.xlsx dg16-grp-pct-savings-3hour-intervals-coolnomix-filtererd.xlsx

cat audit-3hour-for-group-coolnomix.json
[
    ["Grp-Pct-Savings-By-3hr-Bucket", 
            "audit/grp-pct-savings-3hr-by-bucket-coolnomix.sql", {} ],
    ["Grp-Rdngs-Avg-3hr-By-Intvl", "audit/grp-hist-avgs-by-3hr-interval-coolnomix.sql", {} ],
    ["Grp-Rdngs-Det-3hr-By-Time", "audit/grp-hist-det-3hr-by-time-coolnomix.sql", {} ],
    ["Dev-Rdngs-wTemp-By-3hr-Bucket", 
        "audit/dev-hist-w-temp-3hr-by-bucket-coolnomix.sql", {} ],
    ["Dev-Rdngs-3hr-wTemp-By-Time", 
        "audit/dev-hist-w-temp-3hr-by-time-coolnomix.sql", {} ],
    ["Buckets-3hr-By-Temp", 
        "audit/temp-buckets-3hr-by-temp-coolnomix.sql", {} ],
    ["Coolnomix-State-Changes", 
            "audit/coolnomix-state-changes.sql", {} ],
    ["B1400166", 
            "audit/coolnomix-readings-B1400166.sql", {} ],
    ["B1400167", 
            "audit/coolnomix-readings-B1400167.sql", {} ],
    ["B140016A", 
            "audit/coolnomix-readings-B140016A.sql", {} ],
    ["B140016B", 
            "audit/coolnomix-readings-B140016B.sql", {} ]
]

Issue 1.   audit/grp-pct-savings-3hr-by-bucket-coolnomix.sql   for bucket 103 has different counts than does
audit/dev-hist-w-temp-3hr-by-bucket-coolnomix.sql

Reason is that grp query has this FROM clause:
FROM
        device_data_with_intervals3(3, 16) dev
INNER JOIN
        temperature_test_time_adjusted3(3, 16) temp
ON
        temp.test_adjusted_time = dev.test_adjusted_time
INNER JOIN
        temperature_n_hour_intervals(3, 16) temp_n_hr
ON
        temp_n_hr.start_of_interval = dev.start_of_interval

while dev query has this FROM clause:
FROM            
        device_data_with_intervals3(3, 16) dev
INNER JOIN 
        temperature_test_time_adjusted3(3, 16) temp
ON      
        temp.test_adjusted_time = dev.test_adjusted_time
INNER JOIN 
        temperature_n_hour_intervals2(3, 16) temp_n_hr
ON  
        temp_n_hr.start_of_interval = dev.start_of_interval

The dev use of temperature_n_hour_intervals2 yields less rows.  Exactly why is not clear.  Must analyze this further.
plan is to create new spreadsheet plan presents the difference between these two.

JCKalt commented 2 months ago

Explain ENTIRELY missing 103 bucket for modified grp pct savings:

Reproduce existence of 103 bucket

JCKalt commented 2 months ago

Christi TODO List

[x] 1. Create a new dataset in Preset with temp buckets of 3 hours but WITHOUT time restrictions or an ID attached
[x] 2. Then add a rule that removes any numbers with a 0 or below savings
[x] 3. Then add a column to be able to choose the ID and add the NAME next to the ID number in your database
[x] 4. Then add a column to be ale to choose the time restriction applied. One shouold be M-F 7 am to 7pm and one should be 7 days a week
[ ] 5. In the meantime Chris K will contact Preset and look into the posibility of users to be able to create and save their own graphs and dashboards .I know we can do this if we give them a preset login so how do we do that while embedding it.
[ ] 6. Create grp and det -pilot-test-summary datasets based on daily_buckets_view but without weather -- see below

JCKalt commented 2 months ago

Create grp and det -pilot-test-summary datasets based on daily_buckets_view but without weather:

Chirsti has chart using this query below. She wants no weather and display group and it's name to be available

SELECT DATE_TRUNC('day', day_report_date) AS day_report_date, "ONvsOFF" AS "ONvsOFF", sum(total_kwh) AS "SUM(total_kwh)" FROM (SELECT day_report_date, test_start_date, "ONvsOFF", total_off_kwh, total_on_kwh, total_kwh, cost, display_group_id, first_off_total_kwh, first_on_total_kwh, running_total_off_kwh, running_total_on_kwh, running_total_kwh, temp, 100 * (running_total_off_kwh - running_total_on_kwh) / running_total_off_kwh pct_savings FROM daily_buckets_view) AS virtual_table WHERE day_report_date >= TO_DATE('2024-04-30', 'YYYY-MM-DD') AND day_report_date < TO_DATE('2024-07-31', 'YYYY-MM-DD') GROUP BY DATE_TRUNC('day', day_report_date), "ONvsOFF" ORDER BY "SUM(total_kwh)" DESC LIMIT 10000

JCKalt commented 1 month ago

Christi TODO List2 - More urgent that open one above.

[ ] Need to get det-pct-savings-3hr-by-bucket to work with dg 4468 (i.e. join to temperature_n_hour_intervals2(3, 4468)
[ ] remove p_schedule filters on above queries
[ ] Need to focus on her most commonly used dataset: det-pct-savings-3hr-by-bucket and get it to pull in all the display groups.
[ ] auto set start date and time
[ ] test to see if weather comes back using Time zone string

JCKalt commented 1 month ago

Chrsiti's TODO List3 Just came in (8/22 8am)

After some back and forth (was first asking for sheet made with audit-3hour-for-group-coolnomix.json) now apparently wants a run of - audit-3hour.json for dg 4468 but with start time corrected from 1pm to 9am. Need to confirm we have or get if not, weather and data.

WORK: Got weather data since 8/1

JCKalt commented 3 weeks ago

Yesterday Christi mention she didn't see a all the devices she was expecting among these display groups:

5327 - FM Store 1 - Oume Suehiro-chou 5328 - FM Store 2 - Hachouji Akatsuki-chou 5329 - FM Store 3 - Musashimurayama

Today they just showed up. Need to learn about how owner boolean in device table plays a role, because the current database doesn't seem to be aligning with current show-get-ui.ph results among devices in this group.

JCKalt / General-Work

Work on eyedro bucket analysis #214