MaxCompute-ODPS元仓表进行统计分析

1、查询分区表中每天分区的平均条目数和数据量

--查询pro_dwd_measure_gj_prd项目空间的分区表，分区信息中包含202307的表，每天的平均记录数和容量大小。
WITH  table_info  AS (
  SELECT
  SPLIT_PART(table_guid,'.',2)   project_name,
  SPLIT_PART(table_guid,'.',3)   table_name,
  SPLIT_PART(name,'/',2)         part_date,
  SUM(records)     records,
  SUM(data_size)  data_size,
  COUNT(0)        part_num
 FROM pro_ods_sysmetadata_prd.ods_meta1_dpbizmeta_base_meta_partition
 WHERE ds = '20230801' 
 AND  SPLIT_PART(table_guid,'.',2) = 'pro_dwd_measure_gj_prd' 
 AND  SPLIT_PART(name,'/',2)  LIKE  '%202307%' 
 GROUP BY   SPLIT_PART(table_guid,'.',2),SPLIT_PART(table_guid,'.',3),SPLIT_PART(name,'/',2)
 ORDER BY   SPLIT_PART(table_guid,'.',2),SPLIT_PART(table_guid,'.',3),SPLIT_PART(name,'/',2)
)
SELECT
project_name,
table_name,
MAX(records)         max_records,
MIN(records)         min_records,
CEIL(AVG(records))   avg_records,
MAX(data_size)        max_data_size,
MIN(data_size)        min_data_size,
CEIL(AVG(data_size))  avg_data_size,
MAX(part_num)         max_prt_num,
MIN(part_num)         min_prt_num,
CEIL(AVG(part_num))   avg_part_num 
FROM  table_info 
WHERE   records IS NOT NULL AND  records <> 0 
AND   data_size IS NOT NULL AND  data_size <> 0 
GROUP BY project_name,table_name 
ORDER BY project_name,table_name 
;