英文:
Subtract to numbers in relation to time/date specific query in Mysql // Nodered
问题
嗨，作为一个MySQL的新手，我正在尝试在特定的日期/时间查询中减去两个数字：
试图显示我房子的实际每日用电量，我可以通过MODBUS从我的智能电表中使用Node-RED提取KWH的总输入值。但我必须处理它们以查看每日的消耗。
查询的结果应该是：
当天最新条目的输入功率值 - 昨天最后一个条目的输入功率值。
所以当我执行这个查询时，我将得到实际的每日电力需求。
我能够获取最新的值，但我无法通过识别昨天的最后一个条目来进行减法运算。
SELECT inputpwr FROM pwrtable ORDER BY date DESC, time DESC LIMIT 1
可以有人帮忙吗？我不懂SQL。
英文:
hy, beeing a noob in mysql i am trying to substract two numbers in relation to a time/date specific query:
Trying to display the actual daily power consumption of my house i can extract the total input value in KWH via MODBUS using Node red from my Smart Meter. But i have to process them to see the daily consumption.
The result of the query should be:
> value inputpwr of the latest entry of the actual day - inputpwr of last
> entry yesterday.
So when i do this query i will get the actual daily power demand.
I was able to get the latest value, but i am failing to substract them by identifying yesterdays last entry.
SELECT inputpwr FROM pwrtable ORDER BY date DESC,time DESC LIMIT 1
Can anyone help please, im not the SQL guy.
答案1
得分: 0
row_number() 用于按降序顺序为每条记录添加行 ID，然后最新的记录将具有 rn = 1
Lag() 用于基于日期和时间顺序获取前一条记录。
用CTE（公共表达式）如下：
with cte as (
select s.*
from (
SELECT *, row_number() over(partition by _date order by _date desc, _time desc) as rn
FROM pwrtable
) as s
where rn = 1
order by _date desc
)
select _date, inputpwr - lag(inputpwr) over (order by _date, _time) as consumption
from cte
英文:
row_number() to add row id to every records by order desc, then the latest record will be the rn = 1
Lag() to get the previous record based on the order by date and time.
with cte as (
select s.*
from (
SELECT *, row_number() over(partition by _date order by _date desc, _time desc) as rn
FROM pwrtable
) as s
where rn = 1
order by _date desc
)
select _date, inputpwr - lag(inputpwr) over (order by _date, _time) as consumption
from cte
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论