ammar/backend
1
0
Fork 0
mirror of https://github.com/SyncrowIOT/backend.git synced 2025-08-25 22:39:38 +00:00
Code Issues Packages Projects Releases Wiki Activity

Compare commits

base: ammar:feature/test-ai-pr-action
Branches Tags
ammar:main ammar:task/implement-user-booking-settings ammar:hotfix/community-search ammar:task/get-users-with-bookable-spaces-api ammar:task/add-access-dates-to-user-invitaion ammar:dev ammar:DATA-energy-consumed-unit-conversion ammar:task/add-pagination-devices-api ammar:master ammar:https/syncrow.atlassian.net/browse/SP-1857 ammar:feature/test-ai-pr-action ammar:test/ai-pr-description ammar:cdk-aq1 ammar:fix/integrate-cur2-with-schedule ammar:fix-update-aqi-data-on-staging ammar:SP-1736-fe-on-user-management-page-when-i-invited-a-user-as-a-space-member-his-role-appeared-as-admin-in-the-email ammar:fix/add-device-to-firebase-when-comissioning ammar:refactor/add-device-name-to-create-password-api ammar:fix/schedule-device-types ammar:task/curatin-module ammar:add-check-log-to-trace-the-map-issue ammar:temp-fix-to-check ammar:feat/delete-user-account ammar:refactor/increase-rate-limit ammar:fix-time-out-connections-db ammar:DATA-adjust-remaining-procedures ammar:test/prevent-server-block-on-rate-limit ammar:DATA-adjust-procedures ammar:fix/SP-1632-be-get-space-by-id ammar:add-queue-event-handler ammar:revert-426-SP-1778-be-fix-time-out-connections-in-the-db ammar:SP-1778-be-fix-time-out-connections-in-the-db ammar:fix/increase-db-connections-timeout ammar:SP-1780-be-configure-the-curtain-module-device ammar:fix/get-communities-with-spaces ammar:SP-1779-be-add-a-filter-on-get-devices-by-proejct-to-pass-array-of-space-ids ammar:fix/community-structure ammar:task/sort-communities-by-creation-date ammar:fix-staging-insirt-logs-data ammar:DATA-occupancy-count ammar:revamp/space-management ammar:hotfix/power-clamp-hitorical-api ammar:DATA-occupancy-hot-fix ammar:daily-aqi-score ammar:DATA-bug-fix-occupancy-duration-daily ammar:DATA-daily-occupancy-procedure
...
compare: ammar:217a2a8a0de3de6ec864a4f8516b3aa4b48c18da
Branches Tags
ammar:task/implement-user-booking-settings ammar:hotfix/community-search ammar:task/get-users-with-bookable-spaces-api ammar:task/add-access-dates-to-user-invitaion ammar:dev ammar:DATA-energy-consumed-unit-conversion ammar:task/add-pagination-devices-api ammar:main ammar:master ammar:https/syncrow.atlassian.net/browse/SP-1857 ammar:feature/test-ai-pr-action ammar:test/ai-pr-description ammar:cdk-aq1 ammar:fix/integrate-cur2-with-schedule ammar:fix-update-aqi-data-on-staging ammar:SP-1736-fe-on-user-management-page-when-i-invited-a-user-as-a-space-member-his-role-appeared-as-admin-in-the-email ammar:fix/add-device-to-firebase-when-comissioning ammar:refactor/add-device-name-to-create-password-api ammar:fix/schedule-device-types ammar:task/curatin-module ammar:add-check-log-to-trace-the-map-issue ammar:temp-fix-to-check ammar:feat/delete-user-account ammar:refactor/increase-rate-limit ammar:fix-time-out-connections-db ammar:DATA-adjust-remaining-procedures ammar:test/prevent-server-block-on-rate-limit ammar:DATA-adjust-procedures ammar:fix/SP-1632-be-get-space-by-id ammar:add-queue-event-handler ammar:revert-426-SP-1778-be-fix-time-out-connections-in-the-db ammar:SP-1778-be-fix-time-out-connections-in-the-db ammar:fix/increase-db-connections-timeout ammar:SP-1780-be-configure-the-curtain-module-device ammar:fix/get-communities-with-spaces ammar:SP-1779-be-add-a-filter-on-get-devices-by-proejct-to-pass-array-of-space-ids ammar:fix/community-structure ammar:task/sort-communities-by-creation-date ammar:fix-staging-insirt-logs-data ammar:DATA-occupancy-count ammar:revamp/space-management ammar:hotfix/power-clamp-hitorical-api ammar:DATA-occupancy-hot-fix ammar:daily-aqi-score ammar:DATA-bug-fix-occupancy-duration-daily ammar:DATA-daily-occupancy-procedure

5 Commits
feature/te ... 217a2a8a0d

Author SHA1 Message Date
Dona Maria Absi
217a2a8a0d divide 100 2025-07-24 11:45:11 +03:00
Dona Maria Absi
84406fee1b divided 100 2025-07-24 11:41:10 +03:00
Dona Maria Absi
dab09220a5 insert all procedures 2025-07-22 11:25:04 +03:00
Dona Maria Absi
3f2c36d48b consolidated all the procedures into 1 script and removed redundant ones 2025-07-17 12:45:07 +03:00
Dona Maria Absi
ddb205658c divided the energy consumption metric by 100 to convert to kWh 2025-07-15 13:48:00 +03:00
7 changed files with 226 additions and 85 deletions
Expand all files Collapse all files

91
libs/common/src/sql/procedures/fact_device_energy_consumed/procedure_insert_all_fact_daily_device_energy_consumed.sql Normal file
View File

@ -0,0 +1,91 @@
WITH total_energy AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumed'
GROUP BY 1,2,3,4,5
),
energy_phase_A AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedA'
GROUP BY 1,2,3,4,5
),
energy_phase_B AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedB'
GROUP BY 1,2,3,4,5
),
energy_phase_C AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedC'
GROUP BY 1,2,3,4,5
)
, final_data as (
SELECT
t.device_id,
t.date,
t.event_year::text,
t.event_month,
t.hour,
(t.max_value - t.min_value) AS energy_consumed_kW,
(a.max_value - a.min_value) AS energy_consumed_A,
(b.max_value - b.min_value) AS energy_consumed_B,
(c.max_value - c.min_value) AS energy_consumed_C
FROM total_energy t
JOIN energy_phase_A a ON t.device_id = a.device_id AND t.date = a.date AND t.hour = a.hour
JOIN energy_phase_B b ON t.device_id = b.device_id AND t.date = b.date AND t.hour = b.hour
JOIN energy_phase_C c ON t.device_id = c.device_id AND t.date = c.date AND t.hour = c.hour
ORDER BY 1,2)
INSERT INTO public."power-clamp-energy-consumed-daily"(
device_uuid,
energy_consumed_kw,
energy_consumed_a,
energy_consumed_b,
energy_consumed_c,
date
)
SELECT
device_id,
SUM(CAST(energy_consumed_kw AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_a AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_b AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_c AS NUMERIC))::VARCHAR,
date
FROM final_data
GROUP BY device_id, date;

94
libs/common/src/sql/procedures/fact_device_energy_consumed/procedure_insert_all_fact_hourly_device_energy_consumed.sql Normal file
View File

@ -0,0 +1,94 @@
WITH total_energy AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumed'
GROUP BY 1,2,3,4,5
),
energy_phase_A AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedA'
GROUP BY 1,2,3,4,5
),
energy_phase_B AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedB'
GROUP BY 1,2,3,4,5
),
energy_phase_C AS (
SELECT
log.device_id,
log.event_time::date AS date,
EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedC'
GROUP BY 1,2,3,4,5
)
, final_data as (
SELECT
t.device_id,
t.date,
t.event_year::text,
t.event_month,
t.hour,
(t.max_value - t.min_value) AS energy_consumed_kW,
(a.max_value - a.min_value) AS energy_consumed_A,
(b.max_value - b.min_value) AS energy_consumed_B,
(c.max_value - c.min_value) AS energy_consumed_C
FROM total_energy t
JOIN energy_phase_A a ON t.device_id = a.device_id AND t.date = a.date AND t.hour = a.hour
JOIN energy_phase_B b ON t.device_id = b.device_id AND t.date = b.date AND t.hour = b.hour
JOIN energy_phase_C c ON t.device_id = c.device_id AND t.date = c.date AND t.hour = c.hour
ORDER BY 1,2)
INSERT INTO public."power-clamp-energy-consumed-hourly"(
device_uuid,
energy_consumed_kw,
energy_consumed_a,
energy_consumed_b,
energy_consumed_c,
date,
hour
)
SELECT
device_id,
SUM(CAST(energy_consumed_kw AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_a AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_b AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_c AS NUMERIC))::VARCHAR,
date,
hour
FROM final_data
GROUP BY 1,6,7

62
libs/common/src/sql/queries/fact_hourly_energy_consumed/fact_hourly_energy_consumed_insert_statements.sql → libs/common/src/sql/procedures/fact_device_energy_consumed/procedure_insert_all_fact_monthly_device_energy_consumed.sql
View File

@ -5,8 +5,8 @@ WITH total_energy AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log FROM "device-status-log" log
WHERE log.code = 'EnergyConsumed' WHERE log.code = 'EnergyConsumed'
GROUP BY 1,2,3,4,5 GROUP BY 1,2,3,4,5
@ -19,8 +19,8 @@ energy_phase_A AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedA' WHERE log.code = 'EnergyConsumedA'
GROUP BY 1,2,3,4,5 GROUP BY 1,2,3,4,5
@ -33,8 +33,8 @@ energy_phase_B AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedB' WHERE log.code = 'EnergyConsumedB'
GROUP BY 1,2,3,4,5 GROUP BY 1,2,3,4,5
@ -47,8 +47,8 @@ energy_phase_C AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log FROM "device-status-log" log
WHERE log.code = 'EnergyConsumedC' WHERE log.code = 'EnergyConsumedC'
GROUP BY 1,2,3,4,5 GROUP BY 1,2,3,4,5
@ -71,49 +71,6 @@ JOIN energy_phase_C c ON t.device_id = c.device_id AND t.date = c.date AND t.hou
ORDER BY 1,2) ORDER BY 1,2)
INSERT INTO public."power-clamp-energy-consumed-daily"(
device_uuid,
energy_consumed_kw,
energy_consumed_a,
energy_consumed_b,
energy_consumed_c,
date
)
SELECT
device_id,
SUM(CAST(energy_consumed_kw AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_a AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_b AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_c AS NUMERIC))::VARCHAR,
date
FROM final_data
GROUP BY device_id, date;
INSERT INTO public."power-clamp-energy-consumed-hourly"(
device_uuid,
energy_consumed_kw,
energy_consumed_a,
energy_consumed_b,
energy_consumed_c,
date,
hour
)
SELECT
device_id,
SUM(CAST(energy_consumed_kw AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_a AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_b AS NUMERIC))::VARCHAR,
SUM(CAST(energy_consumed_c AS NUMERIC))::VARCHAR,
date,
hour
FROM final_data
GROUP BY 1,6,7
INSERT INTO public."power-clamp-energy-consumed-monthly"( INSERT INTO public."power-clamp-energy-consumed-monthly"(
device_uuid, device_uuid,
energy_consumed_kw, energy_consumed_kw,
@ -131,5 +88,4 @@ SELECT
SUM(CAST(energy_consumed_c AS NUMERIC))::VARCHAR, SUM(CAST(energy_consumed_c AS NUMERIC))::VARCHAR,
TO_CHAR(date, 'MM-YYYY') TO_CHAR(date, 'MM-YYYY')
FROM final_data FROM final_data
GROUP BY 1,6; GROUP BY 1,6;

16
libs/common/src/sql/procedures/fact_device_energy_consumed/fact_daily_device_energy_consumed_procedure.sql → libs/common/src/sql/procedures/fact_device_energy_consumed/procedure_insert_fact_daily_device_energy_consumed.sql
View File

@ -9,8 +9,8 @@ total_energy AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumed' WHERE log.code = 'EnergyConsumed'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date
@ -23,8 +23,8 @@ energy_phase_A AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedA' WHERE log.code = 'EnergyConsumedA'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date
@ -37,8 +37,8 @@ energy_phase_B AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedB' WHERE log.code = 'EnergyConsumedB'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date
@ -51,8 +51,8 @@ energy_phase_C AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedC' WHERE log.code = 'EnergyConsumedC'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date

16
libs/common/src/sql/procedures/fact_device_energy_consumed/fact_hourly_device_energy_consumed_procedure.sql → libs/common/src/sql/procedures/fact_device_energy_consumed/procedure_insert_fact_hourly_device_energy_consumed.sql
View File

@ -9,8 +9,8 @@ total_energy AS (
EXTRACT(HOUR FROM log.event_time)::text AS hour, EXTRACT(HOUR FROM log.event_time)::text AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumed' WHERE log.code = 'EnergyConsumed'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date
@ -23,8 +23,8 @@ energy_phase_A AS (
EXTRACT(HOUR FROM log.event_time)::text AS hour, EXTRACT(HOUR FROM log.event_time)::text AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedA' WHERE log.code = 'EnergyConsumedA'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date
@ -37,8 +37,8 @@ energy_phase_B AS (
EXTRACT(HOUR FROM log.event_time)::text AS hour, EXTRACT(HOUR FROM log.event_time)::text AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedB' WHERE log.code = 'EnergyConsumedB'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date
@ -51,8 +51,8 @@ energy_phase_C AS (
EXTRACT(HOUR FROM log.event_time)::text AS hour, EXTRACT(HOUR FROM log.event_time)::text AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedC' WHERE log.code = 'EnergyConsumedC'
AND log.event_time::date = params.target_date AND log.event_time::date = params.target_date

16
libs/common/src/sql/procedures/fact_device_energy_consumed/fact_monthly_device_energy_consumed_procedure.sql → libs/common/src/sql/procedures/fact_device_energy_consumed/procedure_insert_fact_monthly_device_energy_consumed.sql
View File

@ -9,8 +9,8 @@ total_energy AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumed' WHERE log.code = 'EnergyConsumed'
AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month
@ -23,8 +23,8 @@ energy_phase_A AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedA' WHERE log.code = 'EnergyConsumedA'
AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month
@ -37,8 +37,8 @@ energy_phase_B AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedB' WHERE log.code = 'EnergyConsumedB'
AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month
@ -51,8 +51,8 @@ energy_phase_C AS (
EXTRACT(HOUR FROM log.event_time) AS hour, EXTRACT(HOUR FROM log.event_time) AS hour,
TO_CHAR(log.event_time, 'MM-YYYY') AS event_month, TO_CHAR(log.event_time, 'MM-YYYY') AS event_month,
EXTRACT(YEAR FROM log.event_time)::int AS event_year, EXTRACT(YEAR FROM log.event_time)::int AS event_year,
MIN(log.value)::integer AS min_value, MIN(log.value)::integer/100 AS min_value,
MAX(log.value)::integer AS max_value MAX(log.value)::integer/100 AS max_value
FROM "device-status-log" log, params FROM "device-status-log" log, params
WHERE log.code = 'EnergyConsumedC' WHERE log.code = 'EnergyConsumedC'
AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month AND TO_CHAR(log.event_time, 'MM-YYYY') = params.target_month

16
libs/common/src/sql/queries/fact_daily_device_energy_consumed/fact_daily_device_energy_consumed.sql
View File

@ -4,8 +4,8 @@ WITH total_energy AS (
SELECT SELECT
device_id, device_id,
event_time::date AS date, event_time::date AS date,
MIN(value)::integer AS min_value, MIN(value)::integer/100 AS min_value,
MAX(value)::integer AS max_value MAX(value)::integer/100 AS max_value
FROM "device-status-log" FROM "device-status-log"
where code='EnergyConsumed' where code='EnergyConsumed'
GROUP BY device_id, date GROUP BY device_id, date
@ -15,8 +15,8 @@ WITH total_energy AS (
SELECT SELECT
device_id, device_id,
event_time::date AS date, event_time::date AS date,
MIN(value)::integer AS min_value, MIN(value)::integer/100 AS min_value,
MAX(value)::integer AS max_value MAX(value)::integer/100 AS max_value
FROM "device-status-log" FROM "device-status-log"
where code='EnergyConsumedA' where code='EnergyConsumedA'
GROUP BY device_id, date GROUP BY device_id, date
@ -26,8 +26,8 @@ WITH total_energy AS (
SELECT SELECT
device_id, device_id,
event_time::date AS date, event_time::date AS date,
MIN(value)::integer AS min_value, MIN(value)::integer/100 AS min_value,
MAX(value)::integer AS max_value MAX(value)::integer/100 AS max_value
FROM "device-status-log" FROM "device-status-log"
where code='EnergyConsumedB' where code='EnergyConsumedB'
GROUP BY device_id, date GROUP BY device_id, date
@ -37,8 +37,8 @@ WITH total_energy AS (
SELECT SELECT
device_id, device_id,
event_time::date AS date, event_time::date AS date,
MIN(value)::integer AS min_value, MIN(value)::integer/100 AS min_value,
MAX(value)::integer AS max_value MAX(value)::integer/100 AS max_value
FROM "device-status-log" FROM "device-status-log"
where code='EnergyConsumedC' where code='EnergyConsumedC'
GROUP BY device_id, date GROUP BY device_id, date