选择带时区的最后一个每日非空值 [英] Select last daily not null value with timezone

查看：95
本文介绍了选择带时区的最后一个每日非空值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我收集了许多传感器提供的数据，需要获取每个传感器在特定时区的一天的最后值。

I have a collection of data provided by many sensors and need to get the last value of the day per sensor at a specific timezone.

每个设备都是GEO-

Every device is GEO-located so I need the day located accordingly.

以下是相关表：

CREATE TABLE public.dt_weight(
  hive character(20) NOT NULL,
  hiveconnection integer,
  instant timestamp with time zone NOT NULL,
  weight integer,
  optweight integer,
  CONSTRAINT dt_weight_pkey PRIMARY KEY (hive, instant)
)

然后查询：

SELECT w1.*
    FROM dt_weight w1
    JOIN (
        SELECT hive, DATE_TRUNC('day', instant AT TIME ZONE 'pst') AS moment, 
            MAX(instant) AT TIME ZONE 'pst' AS last 
        FROM dt_weight 
        WHERE weight IS NOT NULL AND hive = '002C0055700833024E45' 
        GROUP BY DATE_TRUNC('day', instant AT TIME ZONE 'pst'), hive
    ) w2
    on (w1.instant = w2.last AND w1.hive=w2.hive)
WHERE w1.hive = '002C0055700833024E45' 
ORDER BY moment

及其结果（蜂巢，时刻，最后）：

and the result (hive, moment, last):

"002C0055700833024E45";932890;"2015-11-23 23:55:42+01";27800;
"002C0055700833024E45";933006;"2015-11-25 23:56:02+01";27770;
"002C0055700833024E45";933065;"2015-11-26 23:56:22+01";27610;

时刻和最后一个返回为没有时区的时间戳，并且丢失了许多记录！单独尝试子查询可以获得更多值：

moment and last are returned as timestamp without timezone and many records are missing! Trying the subquery alone I get more values:

SELECT hive, DATE_TRUNC('day', instant AT TIME ZONE 'pst') AS moment, 
    MAX(instant) AT TIME ZONE 'pst' AS last 
FROM dt_weight 
WHERE weight IS NOT NULL AND hive = '002C0055700833024E45' 
GROUP BY DATE_TRUNC('day', instant AT TIME ZONE 'pst'), hive
ORDER BY last

"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 23:57:17"
"002C0055700833024E45";"2015-11-19 00:00:00";"2015-11-19 23:58:12"
"002C0055700833024E45";"2015-11-20 00:00:00";"2015-11-20 23:52:12"
"002C0055700833024E45";"2015-11-21 00:00:00";"2015-11-21 23:53:03"
"002C0055700833024E45";"2015-11-22 00:00:00";"2015-11-22 23:55:47"
"002C0055700833024E45";"2015-11-23 00:00:00";"2015-11-23 23:55:42"
"002C0055700833024E45";"2015-11-24 00:00:00";"2015-11-24 23:56:02"
"002C0055700833024E45";"2015-11-25 00:00:00";"2015-11-25 23:56:02"
"002C0055700833024E45";"2015-11-26 00:00:00";"2015-11-26 23:56:22"

第18至22天和第24天不显示起来为什么？！？我的客户时区为CEST。需要提及的是，此查询是一个更大计划的一部分。

Days 18 to 22 and 24 doesn't show up. Why?!? My client timezone is CEST. Need to mention that this query is meant as a piece of a bigger plan.

为完整起见，以下是部分源数据：

For completeness here's a slice of source data:

SELECT hive, DATE_TRUNC('day', instant AT TIME ZONE 'pst') AS moment, instant AT TIME ZONE 'pst', weight
FROM dt_weight
WHERE weight IS NOT NULL AND hive = '002C0055700833024E45' 
ORDER BY instant

"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 03:04:57";3540
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 03:05:17";3540
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 06:47:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 06:57:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 07:07:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 07:17:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 07:27:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 07:37:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 07:47:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 07:57:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 08:07:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 08:17:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 08:27:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 08:37:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 08:47:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 08:57:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 09:07:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 09:17:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 09:27:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 09:37:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 09:47:27";0
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 09:57:27";27940
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 10:07:27";27940
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 10:17:27";27950
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 10:27:27";27960
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 10:37:27";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 10:47:27";27950
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 10:57:27";27950
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 11:07:27";27970
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 11:17:27";27960
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 11:27:27";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 11:37:27";27970
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 11:47:27";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 11:57:27";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 12:07:27";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 12:17:27";27970
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 12:27:27";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 12:37:27";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 12:47:27";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 12:57:27";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 13:07:27";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 13:17:27";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 13:27:27";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 13:37:27";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 13:47:27";28020
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 13:57:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 14:07:23";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 14:17:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 14:27:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 14:37:23";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 14:47:23";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 14:57:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 15:07:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 15:17:23";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 15:27:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 15:37:23";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 15:47:23";28020
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 15:57:23";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 16:07:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 16:17:23";27960
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 16:27:23";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 16:37:23";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 16:47:23";27970
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 16:57:23";27990
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 17:07:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 17:17:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 17:27:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 17:37:23";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 17:47:23";27970
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 17:57:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 18:07:23";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 18:17:23";28030
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 18:27:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 18:37:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 18:47:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 18:57:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 19:07:23";28040
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 19:17:23";28030
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 19:27:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 19:37:23";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 19:47:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 19:57:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 20:07:23";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 20:17:23";27980
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 20:27:23";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 20:37:23";28040
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 20:47:23";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 20:57:17";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 21:07:17";28050
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 21:17:17";28020
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 21:27:17";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 21:37:17";28020
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 21:47:17";28020
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 21:57:17";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 22:07:17";28030
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 22:17:17";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 22:27:17";28030
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 22:37:17";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 22:47:17";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 22:57:17";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 23:07:17";28040
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 23:17:17";28020
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 23:27:17";28000
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 23:37:17";28010
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 23:47:17";28020
"002C0055700833024E45";"2015-11-18 00:00:00";"2015-11-18 23:57:17";28010
"002C0055700833024E45";"2015-11-19 00:00:00";"2015-11-19 00:07:17";28030
"002C0055700833024E45";"2015-11-19 00:00:00";"2015-11-19 00:17:17";28010
"002C0055700833024E45";"2015-11-19 00:00:00";"2015-11-19 00:27:17";28010


推荐答案

尝试以下方法：

SELECT
    DISTINCT ON (hive, moment)
    *, DATE_TRUNC('day', instant AT TIME ZONE 'pst') AS moment
FROM dt_weight
ORDER BY hive, moment DESC, instant DESC;

它使用 DISTINCT ON 仅保留配置单元和日期的每种组合的第一行。为了确保我们保留的第一行也是最新的，我们还按相反的时间顺序进行排序。

It uses DISTINCT ON to keep only the first row for each combination of hive and day. To make sure that the first row that we keep is also the latest one, we additionally sort in reverse chronological order.

这篇关于选择带时区的最后一个每日非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆