IOT 获取物联网设备最新记录

物联网设备上报状态信息表

-- public.local_product_dev_data_status definition

-- Drop table

-- DROP TABLE public.local_product_dev_data_status;

CREATE TABLE public.local_product_dev_data_status (
	product_dev_data_status_id int4 NOT NULL,
	product_id varchar(40) NULL,
	product_key varchar(25) NULL,
	gprs_gd varchar(30) NULL,
	device_name varchar(50) NULL,
	temperature float4 NULL,
	humidity float4 NULL,
	rain int2 NULL,
	particulate_matter int2 NULL,
	noise int2 NULL,
	liquid_level int2 NULL,
	waste_level int2 NULL,
	valve_position varchar(30) NULL,
	voltage float4 NULL,
	voltage1 float4 NULL,
	voltage2 float4 NULL,
	pwr_mode int2 NULL,
	extend_param varchar(40) NULL,
	base_content_ali varchar(255) NULL,
	createdon timestamp(6) NULL,
	createdby varchar(40) NULL,
	modifiedby varchar(40) NULL,
	modifiedon timestamp(6) NULL,
	deletion_state varchar(1) NULL,
	description varchar(20) NULL,
	virtual_dev int2 NULL,
	ctrl_mode varchar(15) NULL,
	CONSTRAINT local_product_dev_data_status_pkey PRIMARY KEY (product_dev_data_status_id)
);
CREATE INDEX idx_createdon_dev_product ON public.local_product_dev_data_status USING btree (createdon, device_name, product_key, deletion_state);
CREATE INDEX idx_gin_product_dev_created_vala ON public.local_product_dev_data_status USING gin (product_key, device_name, valve_position, createdon, modifiedon);

POSTGRESQL sql获取具体设备的最新记录

WITH RECURSIVE T AS (    
                 (    
                   SELECT    
                     local_product_dev_data_status AS local_product_dev_data_status  
                   FROM    
                     local_product_dev_data_status    
                   WHERE    
                     device_name = #{deviceName}    
                   AND product_key = #{produceKey}     
                   ORDER BY    
                     device_name,    
                     createdon DESC    
                   LIMIT 1    
                 )    
                 UNION ALL    
                   (    
                     SELECT    
                       (    
                         SELECT    
                           t1    
                         FROM    
                           local_product_dev_data_status AS t1    
                         WHERE    
                           t1.device_name > (    
                             T .local_product_dev_data_status ).device_name    
                             AND  t1.device_name = #{deviceName}  and t1.product_key = #{produceKey}     
                               
                             ORDER BY    
                               device_name,    
                               createdon DESC    
                             LIMIT 1    
                           )    
                         FROM    
                           T    
                         WHERE    
                           (    
                             T .local_product_dev_data_status ).device_name IS NOT NULL    
                           )    
                       ) SELECT    
                         (    
                           T .local_product_dev_data_status ).*    
                           FROM    
                             T    
                           WHERE    
                             T .* IS NOT NULL    
                           AND (T .local_product_dev_data_status).product_key = #{produceKey}     
                             AND (T .local_product_dev_data_status).device_name = #{deviceName} 

 with recursive T as (    
                 (
select
	local_product_dev_data_status as local_product_dev_data_status
from
	local_product_dev_data_status
where
	device_name = 'afomWfwio'
	and product_key = 'a10jIbnMOEI'
order by
	device_name,
	createdon desc
limit 1    
                 )
union all    
                   
(
select    
                       (
select
	t1
from
	local_product_dev_data_status as t1
where
	t1.device_name > (    
                             T .local_product_dev_data_status ).device_name
		and t1.device_name = 'afomWfwio'
		and t1.product_key = 'a10jIbnMOEI'
	order by
		device_name,
		createdon desc
	limit 1    
                           )
from    
                           T
where    
                           (    
                             T .local_product_dev_data_status ).device_name is not null    
                           )    
                       )
select
	(    
                           T .local_product_dev_data_status ).*
from
	T
where
	T .* is not null
	and (T .local_product_dev_data_status).product_key = 'a10jIbnMOEI'
	and (T .local_product_dev_data_status).device_name = 'afomWfwio'

获取所有设备的最新记录