Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- +--------------+-----------+-----------------------+
- | energy_usage | device_id | timestamp |
- +--------------+-----------+-----------------------+
- | 10 | 1 | 2019-02-12T01:00:00 |
- | 16 | 2 | 2019-02-12T01:00:00 |
- | 26 | 1 | 2019-03-12T02:00:00 |
- | 24 | 2 | 2019-03-12T02:00:00 |
- +--------------+-----------+-----------------------+
- +--------------+------------------+--------------------+-----------+------------+
- | energy_usage | energy_usage_day | energy_usage_night | device_id | date |
- +--------------+------------------+--------------------+-----------+------------+
- | 80 | 30 | 50 | 1 | 2019-06-02 |
- | 130 | 60 | 70 | 2 | 2019-06-03 |
- +--------------+------------------+--------------------+-----------+------------+
- WITH temp AS (
- SELECT *, SUM(usage) OVER(win) > 50 qualified,
- SUM(usage) OVER(win) - 50 rolling_sum,
- EXTRACT(HOUR FROM timestamp) BETWEEN 8 AND 19 day_hour,
- EXTRACT(MONTH FROM timestamp) month,
- FORMAT_TIMESTAMP("%Y-%m-%d", timestamp) date
- FROM `project.dataset.table`
- WINDOW win AS (PARTITION BY device_id, TIMESTAMP_TRUNC(timestamp, MONTH) ORDER BY timestamp)
- ), temp_with_adjustments AS (
- SELECT *,
- IF(
- ROW_NUMBER() OVER(PARTITION BY device_id, MONTH ORDER BY timestamp) = 1,
- rolling_sum,
- usage
- ) AS adjusted_energy_usage
- FROM temp
- WHERE qualified
- )
- SELECT ROUND(SUM(adjusted_energy_usage), 4) energy_usage,
- ROUND(SUM(IF(day_hour, adjusted_energy_usage, 0)), 4) energy_usage_day,
- ROUND(SUM(IF(NOT day_hour, adjusted_energy_usage, 0)), 4) energy_usage_night,
- device_id,
- date
- FROM temp_with_adjustments
- GROUP BY device_id, date
- +--------------+-----------+-----------------------+
- | usage_charge | device_id | timestamp |
- +--------------+-----------+-----------------------+
- | 0.2 | 1 | 2019-02-12T01:00:00 |
- | 0.6 | 2 | 2019-02-12T01:00:00 |
- | 0.1 | 1 | 2019-03-12T02:00:00 |
- | 1.2 | 2 | 2019-03-12T02:00:00 |
- +--------------+-----------+-----------------------+
- +--------------+------------------+--------------------+--------------+------------------+--------------------+-----------+------------+
- | energy_usage | energy_usage_day | energy_usage_night | usage_charge | usage_charge_day | usage_charge_night | device_id | date |
- +--------------+------------------+--------------------+--------------+------------------+--------------------+-----------+------------+
- | 80 | 30 | 50 | 1.2 | 0.4 | 0.8 | 1 | 2019-06-02 |
- | 130 | 60 | 70 | 2.5 | 1 | 1.5 | 2 | 2019-06-03 |
- +--------------+------------------+--------------------+--------------+------------------+--------------------+-----------+------------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement