odps基础积累-常用知识

场景:
    基础知识积累
说明:
    由于互联网产品更新迭代非常快,记录的知识可能只适合当下一段时间,查询最新最权威文档移步官网www.aliyun.com
1.删除分区

ALTER table SENSOR_COLLECT_DATA drop if exists partition(data_date = '${v_data_date}');

2.初始化分区

ALTER table SENSOR_COLLECT_DATA add if not exists partition(data_date = '${v_data_date}');

3.获取序列

SELECT
  CONCAT(
    UNIX_TIMESTAMP(getdate ()),
    ROW_NUMBER () OVER (PARTITION BY 1
      ORDER BY UUID() DESC)
  ) AS idNum
FROM
  SENSOR_COLLECT_DATA;

4.去重

SELECT * FROM(
SELECT T.*,
       ROW_NUMBER() OVER(PARTITION BY t.sensor_id ORDER BY t.region ASC, t.data_type) AS NM
  FROM SENSOR_COLLECT_DATA T
 WHERE T.sensor_id = '20191501'
) TT WHERE TT.NM=1 ;

5.附建表语句

create table `SENSOR_COLLECT_DATA`
(
  `sensor_id`        BIGINT COMMENT '数据id实体唯一标识',    
  `data_type`      BIGINT COMMENT '数据类型(1:温度、2:湿度)',     
  `region`         STRING COMMENT '传感器安装区域',
  `s1`       DOUBLE COMMENT '传感器采集的值1',        
  `s2`       DOUBLE COMMENT '传感器采集的值2',       
  `s3`       DOUBLE COMMENT '传感器采集的值3',       
  `s4`       DOUBLE COMMENT '传感器采集的值4',       
  `s5`       DOUBLE COMMENT '传感器采集的值5',       
  `s6`       DOUBLE COMMENT '传感器采集的值6',       
  `s7`       DOUBLE COMMENT '传感器采集的值7',       
  `s8`       DOUBLE COMMENT '传感器采集的值8',       
  `s9`       DOUBLE COMMENT '传感器采集的值9',       
  `s10`      DOUBLE COMMENT '传感器采集的值10',       
  `s11`      DOUBLE COMMENT '传感器采集的值11',       
  `s12`      DOUBLE COMMENT '传感器采集的值12'
)
COMMENT '传感器采集数据'
PARTITIONED BY (
	data_date STRING COMMENT '数据日期'
)
LIFECYCLE 100000;

以上,感谢.