CREATE TABLE `iot_workdata2` (
`day` int(11) NULL,
`vehicleId` varchar(200) NULL,
`gpsTime` datetime NULL,
`dorisTime` datetime default current_timestamp,
`cmdId` int(11) NULL,
`serialNo` int(11) NULL,
`systemTime` datetime NULL,
`content` variant NULL,
`raw` String,
`terminalNo` varchar(255),
`vinCode` varchar(255),
`type` tinyint
) unique key(day, vehicleId, gpsTime)
partition by range(day)(
partition p_202401 values less than (20240201)
)
distributed by hash(vehicleId) buckets 8
properties(
"replication_num" = "1"
);
create view originalspectrum as
select *
from (
SELECT vehicleid ,gpstime ,
COALESCE (cast(content['admindivisioncode'] as varchar),'') AS admindivisioncode,
cast(content['TotalEngineHours'] as double) AS TotalEngineHours,
cast(content['TotalEngineFuel'] as double) AS TotalFuelConsum,
-- 动臂升
case when content['dongbeishengxiandaoyali']>0.7 then 1 else 0 end AS dongbiup,
ROW_NUMBER() over (partition by vehicleid ,gpstime ) as rownum
from iot_workdata
where `type`=1 and `day` =20240309 and content['TotalEngineHours'] is not null
) t
where rownum=1
;
创建视图时报错：SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Unexpected exception: null，select语句单独执行执行
