版本: 4.2.5
CDB_OB_TABLET_REPLICAS没法筛选leader分区，是不是得用CDB_OB_TABLE_LOCATIONS ？
SELECT
database_name,
SVR_IP,
COUNT(DISTINCT PARTITION_NAME) AS partition_count,
COUNT(DISTINCT SUBPARTITION_NAME) AS subpartition_count
FROM CDB_OB_TABLE_LOCATIONS
WHERE role = ‘LEADER’
AND PARTITION_NAME IS NOT NULL
AND SUBPARTITION_NAME IS NOT NULL
GROUP BY database_name,svr_ip order by database_name;
4.2.5 版本统计节点 Leader 分区，使用CDB_OB_TABLE_LOCATIONS视图，CDB_OB_TABLET_REPLICAS无 leader 角色字段无法筛选主副本。原 SQL 存在缺陷，SUBPARTITION_NAME IS NOT NULL会过滤普通一级分区，造成统计缺失。统计时不要直接过滤子分区字段，通过 case 拼接唯一标识分别统计一级分区、子分区。通过role='LEADER'过滤，只统计本节点为主副本的分区，排除 follower。可增加svr_ip='ip地址'条件，只查询单个 observer 节点。
SELECT database_name,svr_ip,
COUNT(DISTINCT CASE WHEN partition_name IS NOT NULL THEN CONCAT(database_name,’.’,table_id,’:’,partition_name) END) AS partition_count,
COUNT(DISTINCT CASE WHEN subpartition_name IS NOT NULL THEN CONCAT(database_name,’.’,table_id,’:’,partition_name,’:’,subpartition_name) END) AS subpartition_count
FROM CDB_OB_TABLE_LOCATIONS
WHERE role = ‘LEADER’
GROUP BY database_name, svr_ip ORDER BY database_name,svr_ip;