postgresql SQLAlchemy分组为30分钟间隔的问题

31moq8wy 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(142)

对于这里的所有SQLMaven，我正在尝试编写一个查询，以从下表中获得一天中每30分钟的总能耗。
要了解更多信息，kwh存储从安装到电网的设备发送的值，其中设备将在某个间隔基础上报告当前电表。（仅当有电流流动时，否则将没有数据。）
下表是一个简化表。

class EnergyLogTable(BaseModel):
__tablename__ = 'energy_log_table'
kwh = db.Column(db.Numeric(scale=2), default=0.0)
created_at = db.Column(db.DateTime(timezone=True), nullable=False)

字符串
这是我目前的问题，

date_label = 'hour'

timestamp = func.date_trunc('minute', models.EnergyDispensesLog.created_at)
epoch_seconds = func.extract('epoch', timestamp).label('epoch_seconds')
interval = cast(cast(epoch_seconds, Integer) / 1800, Integer) * 1800

base_query = (
    select(
        func.to_timestamp(interval).label(date_label),
        (func.max(models.EnergyLogTable.kwh) - func.min(models.EnergyLogTable.kwh)).label('energy_spent')
    ).where(
        models.EnergyLogTable.created_at >= from_date,
        models.EnergyLogTable.created_at < to_date,
        models.EnergyLogTable.kwh > 0,
    ).group_by(interval, date_label).order_by(date_label)
)

型
示例数据：

created_at                      kwh
"2023-10-25 06:56:18.015066+00" 559.40
"2023-10-25 06:57:15.512495+00" 559.58
"2023-10-25 06:58:15.482616+00" 559.75
"2023-10-25 06:59:15.510724+00" 559.92
"2023-10-25 07:00:15.551606+00" 560.11
"2023-10-25 07:01:15.453912+00" 560.28
"2023-10-25 07:02:15.38114+00"  560.47
"2023-10-25 07:03:15.346221+00" 560.63
"2023-10-25 07:04:15.426906+00" 560.82
"2023-10-25 07:05:15.412163+00" 561.00
"2023-10-25 07:06:15.48954+00"  561.16
"2023-10-25 07:07:15.388114+00" 561.35
"2023-10-25 07:08:15.404986+00" 561.52
"2023-10-25 07:09:15.417893+00" 561.71
"2023-10-25 07:10:15.39941+00"  561.88
"2023-10-25 07:11:15.448669+00" 562.07
"2023-10-25 07:12:15.557211+00" 562.24
"2023-10-25 07:13:15.397659+00" 562.41
"2023-10-25 07:14:15.47744+00"  562.60
"2023-10-25 07:15:15.528852+00" 562.77
"2023-10-25 07:16:15.421955+00" 562.96
"2023-10-25 07:17:15.411252+00" 563.12
"2023-10-25 07:18:15.449404+00" 563.30
"2023-10-25 07:19:15.459984+00" 563.49
"2023-10-25 07:20:15.468558+00" 563.66
"2023-10-25 07:21:15.47227+00"  563.85
"2023-10-25 07:22:15.350083+00" 564.02
"2023-10-25 07:23:15.644026+00" 564.20
"2023-10-25 07:24:15.454081+00" 564.37
"2023-10-25 07:25:15.43371+00"  564.55
"2023-10-25 07:26:15.573115+00" 564.74
"2023-10-25 07:27:15.557926+00" 564.91
"2023-10-25 07:28:15.43053+00"  565.09
"2023-10-25 07:29:15.558076+00" 565.27
"2023-10-25 07:30:15.419831+00" 565.45
"2023-10-25 07:31:15.548472+00" 565.62
"2023-10-25 07:32:15.498099+00" 565.80
"2023-10-25 07:33:15.332133+00" 565.99
"2023-10-25 07:34:15.463259+00" 566.16
"2023-10-25 07:35:15.491288+00" 566.35
"2023-10-25 07:36:15.485671+00" 566.51
"2023-10-25 07:37:15.395341+00" 566.70
"2023-10-25 07:38:15.401426+00" 566.87
"2023-10-25 07:39:15.414545+00" 567.05
"2023-10-25 07:40:15.436152+00" 567.24
"2023-10-25 07:41:15.38887+00"  567.40
"2023-10-25 07:42:15.484769+00" 567.60
"2023-10-25 07:43:15.484849+00" 567.76
"2023-10-25 07:44:15.448255+00" 567.95
"2023-10-25 07:45:15.507435+00" 568.12
"2023-10-25 07:46:15.450651+00" 568.30
"2023-10-25 07:47:15.418091+00" 568.49
"2023-10-25 07:48:15.320837+00" 568.66
"2023-10-25 07:49:15.388797+00" 568.84
"2023-10-25 07:50:15.571942+00" 569.01
"2023-10-25 07:51:15.324393+00" 569.20
"2023-10-25 07:52:15.321933+00" 569.37
"2023-10-25 07:53:15.515515+00" 569.54
"2023-10-25 07:54:15.546828+00" 569.73
"2023-10-25 07:55:15.518291+00" 569.90
"2023-10-25 07:56:15.522586+00" 570.09
"2023-10-25 07:57:15.537949+00" 570.26
"2023-10-25 07:58:15.401685+00" 570.44
"2023-10-25 07:59:15.335498+00" 570.62
"2023-10-25 08:00:11.204091+00" 570.76

型
查询似乎在工作，直到在例如xx：59和xx：01标记之间有流量计时为止。
对于06：30至07：00的范围，它使用最高值559.92减去最低值559.40，得到0.52 kWh。
下一个时间段07：00到07：30，它使用565.27-560.11=5.16，等等...
因此，xx：59至xx：01之间或xx：29至xx：31之间使用的能量不包括在计算中。
如果我有可能获得下一个时间段的第一个值来进行计算，我将寻求帮助。例如，对于06：30到07：00的时间段，我们的计算结果是560.11-559.40，而不是559.92-559.40。如果有更好的方法或解决方案来解决此问题，我们将不胜感激。
谢谢

postgresql

来源：https://stackoverflow.com/questions/77364050/sqlalchemy-problem-with-grouping-into-30-minutes-interval

1条答案

按热度按时间

n6lpvg4x1#

晚到党，简单地复制我会使用postgresql查询根据您的SQLAlchemy查询.您想要的输出需要使用lead函数后groupby，铅值为nextMinKwh，如果nextMinKwh为null，这意味着这是最新的值到目前为止.

SELECT *, 
    CASE 
        WHEN nextMinKwh IS NOT NULL THEN nextMinKwh - minKwh 
        ELSE maxKwh - minKwh 
    END AS payKwh 
FROM (
    SELECT
        to_timestamp(interval) AS date_label,
        max(kwh) AS maxKwh,
        min(kwh) AS minKwh,
        sum(kwh) AS sumKwh,
        min(created_at) AS minTime,
        max(created_at) AS maxTime,
        lead(min(kwh)) OVER (ORDER BY interval) AS nextMinKwh
    FROM (
        SELECT 
            kwh, 
            created_at,
            extract('epoch' FROM date_trunc('minute', created_at))::integer AS epoch_seconds,
            (extract('epoch' FROM date_trunc('minute', created_at))::integer / 1800)::integer * 1800 AS interval
        FROM energylogtable
    ) AS t1
    GROUP BY date_label, interval
    ORDER BY date_label
) t2;

字符串
数据

create table EnergyLogTable(
    id serial primary key,
    Created_At timestamp,
    Kwh float
);

insert into EnergyLogTable(Created_At, Kwh) values 
('2023-10-25 06:56:18.015066', 559.40),
('2023-10-25 06:57:15.512495', 559.58),
('2023-10-25 06:58:15.482616',559.75),
('2023-10-25 06:59:15.510724',559.92),
('2023-10-25 07:00:15.551606',560.11),
('2023-10-25 07:01:15.453912',560.28),
('2023-10-25 07:02:15.38114', 560.47),
('2023-10-25 07:03:15.346221',560.63),
('2023-10-25 07:04:15.426906',560.82),
('2023-10-25 07:05:15.412163',561.00),
('2023-10-25 07:06:15.48954', 561.16),
('2023-10-25 07:07:15.388114',561.35),
('2023-10-25 07:08:15.404986',561.52),
('2023-10-25 07:09:15.417893',561.71),
('2023-10-25 07:10:15.39941', 561.88),
('2023-10-25 07:11:15.448669',562.07),
('2023-10-25 07:12:15.557211',562.24),
('2023-10-25 07:13:15.397659',562.41),
('2023-10-25 07:14:15.47744', 562.60),
('2023-10-25 07:15:15.528852',562.77),
('2023-10-25 07:16:15.421955',562.96),
('2023-10-25 07:17:15.411252',563.12),
('2023-10-25 07:18:15.449404',563.30),
('2023-10-25 07:19:15.459984',563.49),
('2023-10-25 07:20:15.468558',563.66),
('2023-10-25 07:21:15.47227', 563.85),
('2023-10-25 07:22:15.350083',564.02),
('2023-10-25 07:23:15.644026',564.20),
('2023-10-25 07:24:15.454081',564.37),
('2023-10-25 07:25:15.43371', 564.55),
('2023-10-25 07:26:15.573115',564.74),
('2023-10-25 07:27:15.557926',564.91),
('2023-10-25 07:28:15.43053', 565.09),
('2023-10-25 07:29:15.558076',565.27),
('2023-10-25 07:30:15.419831',565.45),
('2023-10-25 07:31:15.548472',565.62),
('2023-10-25 07:32:15.498099',565.80),
('2023-10-25 07:33:15.332133',565.99),
('2023-10-25 07:34:15.463259',566.16),
('2023-10-25 07:35:15.491288',566.35),
('2023-10-25 07:36:15.485671',566.51),
('2023-10-25 07:37:15.395341',566.70),
('2023-10-25 07:38:15.401426',566.87),
('2023-10-25 07:39:15.414545',567.05),
('2023-10-25 07:40:15.436152',567.24),
('2023-10-25 07:41:15.38887', 567.40),
('2023-10-25 07:42:15.484769',567.60),
('2023-10-25 07:43:15.484849',567.76),
('2023-10-25 07:44:15.448255',567.95),
('2023-10-25 07:45:15.507435',568.12),
('2023-10-25 07:46:15.450651',568.30),
('2023-10-25 07:47:15.418091',568.49),
('2023-10-25 07:48:15.320837',568.66),
('2023-10-25 07:49:15.388797',568.84),
('2023-10-25 07:50:15.571942',569.01),
('2023-10-25 07:51:15.324393',569.20),
('2023-10-25 07:52:15.321933',569.37),
('2023-10-25 07:53:15.515515',569.54),
('2023-10-25 07:54:15.546828',569.73),
('2023-10-25 07:55:15.518291',569.90),
('2023-10-25 07:56:15.522586',570.09),
('2023-10-25 07:57:15.537949',570.26),
('2023-10-25 07:58:15.401685',570.44),
('2023-10-25 07:59:15.335498',570.62),
('2023-10-25 08:00:11.204091',570.76),
('2023-10-26 07:58:15.335498',571.62),
('2023-10-26 08:10:11.204091',572.76),
('2023-10-27 06:19:15.335498',573.62),
('2023-10-27 06:58:11.204091',574.76)

型
结果
| 日期标签|最大千瓦时|明奎|苏姆夸|分钟时间|最大时间|下明基|佩奎|
| --|--|--|--|--|--|--|--|
| 2023-10-25T06：30：00+00：00|五百五十九点九二|五百五十九点四|2238.65| 2023-10-25T06：56：18.015066| 2023-10-25T06：59：15.510724| 560.11| 0.710000000000364|
| 2023-10-25T07：00：00+00：00| 565.27| 560.11| 16880.55| 2023-10-25T07：00：15.551606| 2023-10-25T07：29：15.558076| 565.45| 5.34000000000032|
| 2023-10-25T07：30：00+00：00| 570.62| 565.45| 17041.02| 2023-10-25T07：30：15.419831| 2023-10-25T07：59：15.335498| 570.76| 5.3099999999945|
| 2023-10-25T08：00：00+00：00| 570.76| 570.76| 570.76| 2023-10-25T08：00：11.204091| 2023-10-25T08：00：11.204091| 571.62| 0.860000000000136|
| 2023-10-26T07：30：00+00：00| 571.62| 571.62| 571.62| 2023-10-26T07：58：15.335498| 2023-10-26T07：58：15.335498| 572.76| 1.13999999999864|
| 2023-10-26T08：00：00+00：00| 572.76| 572.76| 572.76| 2023-10-26T08：10：11.204091| 2023-10-26T08：10：11.204091| 573.62| 0.860000000000136|
| 2023-10-27T06：00：00+00：00| 573.62| 573.62| 573.62| 2023-10-27T06：19：15.335498| 2023-10-27T06：19：15.335498| 574.76| 1.13999999999864|
| 2023-10-27T06：30：00+00：00| 574.76| 574.76| 574.76| 2023-10-27T06：58：11.204091| 2023-10-27T06：58：11.204091| NULL| 0.0个单位|

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql SQLAlchemy分组为30分钟间隔的问题

1条答案

相关问题

热门标签

最新问答