版本 doris-2.1.5-rc02-d5a02e095d

表结构

CREATE TABLE `ods_yuan_tb_dialogue_bak` (
  `id` INT NOT NULL COMMENT '',
  `responsible_num` VARCHAR(65533) NULL COMMENT '',
  `status` TINYINT NOT NULL DEFAULT "0" COMMENT '',
  `first_send_time` DATETIME(3) NULL COMMENT '',
  `manual_first_send_time` DATETIME NULL COMMENT '',
  `evaluate_status` TINYINT NULL COMMENT '',
  `evaluate_score` TINYINT NULL COMMENT '',
  `evaluate_solved` TINYINT NULL COMMENT ''
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT '会话表'
DISTRIBUTED BY HASH(`id`) BUCKETS 3
PROPERTIES (
	"replication_allocation" = "tag.location.default: 2",
	"min_load_replica_num" = "-1",
	"is_being_synced" = "false",
	"storage_medium" = "hdd",
	"storage_format" = "V2",
	"inverted_index_storage_format" = "V1",
	"enable_unique_key_merge_on_write" = "false",
	"light_schema_change" = "true",
	"disable_auto_compaction" = "false",
	"enable_single_replica_compaction" = "false",
	"group_commit_interval_ms" = "10000",
	"group_commit_data_bytes" = "134217728"
);

数据样例

数据样例私发给社区人员

查询语句

with base_list as(
	select 
		*,
		if(manual_first_send_time is not null,seconds_diff(first_send_time ,manual_first_send_time),0) as response_seconds,
		1 as is_work
	from 
    ods_yuan_tb_dialogue_bak 
    where responsible_num is not null
)
select 
responsible_num as responsibleNum,
IFNULL(reception_count,0) as receptionCount,
IFNULL(reception_count_radio,0) as receptionCountRadio,
if(workday_timely_response_radio is null or workday_timely_response_radio <= 0,'-',workday_timely_response_radio) as workdayTimelyResponseRadio,
if(holiday_timely_response_radio is null or holiday_timely_response_radio <= 0,'-',holiday_timely_response_radio) as holidayTimelyResponseRadio,
if(workday_response_minutes is null or workday_response_minutes <= 0,'-',workday_response_minutes) as workdayResponseMinutes,
if(holiday_response_minutes is null or holiday_response_minutes <= 0,'-',holiday_response_minutes) as holidayResponseMinutes,
if(manual_avg_evaluate_score is null or manual_avg_evaluate_score <= 0,'-',manual_avg_evaluate_score) as manualAvgEvaluateScore,
if(manual_good_evaluate_radio is null or manual_good_evaluate_radio <= 0,'-',manual_good_evaluate_radio) as manualGoodEvaluateRadio,
if(manual_evaluate_count is null or manual_evaluate_count <= 0,'-',manual_evaluate_count) as manualEvaluateCount,
if(manual_solved_count is null or manual_solved_count <= 0,'-',manual_solved_count) as manualSolvedCount
from 
(
	select 
	 	responsible_num,
	 	count(*) as reception_count,
	    ROUND(count(*)/SUM(COUNT(*)) OVER ()*100,2) AS reception_count_radio,
		ROUND(SUM(if(response_seconds < 180 and is_work = 1,1,0))/count(*) * 100,2) as workday_timely_response_radio,
		ROUND(SUM(if(response_seconds < 180 and is_work = 0,1,0))/count(*) * 100,2) as holiday_timely_response_radio,
		ROUND(SUM(if(is_work = 1,response_seconds,0))/SUM(IF(is_work = 1,1,0)) /60,2) as workday_response_minutes,
		ROUND(SUM(if(is_work = 0,response_seconds,0))/SUM(IF(is_work = 0,1,0)) /60,2) as holiday_response_minutes,
	    ROUND(SUM(IF(evaluate_status = 1,evaluate_score,0))/SUM(IF(evaluate_status = 1,1,0)),2) as manual_avg_evaluate_score,
	    IFNULL(ROUND((1-SUM(IF(evaluate_score <= 3,1,0)) / COUNT(*)) *100,2),0) as manual_good_evaluate_radio,
	    SUM(IF(evaluate_status = 1,1,0)) as manual_evaluate_count,
	    SUM(IF(evaluate_solved = 1,1,0)) as manual_solved_count
	from
	base_list
	group by responsible_num 
) t
order by workdayTimelyResponseRadio desc;  -- 排序错误


order by receptionCountRadio desc;   --排序正确

表象： 部分字段的排序正确，部分的错误。