如何查询客服数据表实现同日期跨周期数据对比与多维度指标计算

Alright, let's break down how to build this comparative customer service report. The key is to pull data for the selected date, same day last month, and same day last year, then calculate the required metrics like success rates, rankings, and percentage differences. Here's a step-by-step solution using SQL with common table expressions (CTEs) for clarity:

Step 1: Define Date Parameters & Core Data CTEs #

First, we'll convert the input date format to a database-compatible date type, then fetch data for the three target dates while calculating baseline metrics:

-- Set input date (replace ${call_date} with your report tool's parameter)
SET @input_date = STR_TO_DATE('${call_date}', '%d/%m/%Y');
SET @last_month_date = DATE_SUB(@input_date, INTERVAL 1 MONTH);
SET @last_year_date = DATE_SUB(@input_date, INTERVAL 1 YEAR);

WITH current_data AS (
    -- Get selected date data + current success rate + user's current rank
    SELECT
        userid,
        calls_received AS cl_cur,
        calls_answered AS ans_cur,
        calls_rejected AS rej_cur,
        ROUND((calls_answered / calls_received)*100, 1) AS success_percentage,
        RANK() OVER (ORDER BY (calls_answered / calls_received) DESC) AS current_rank
    FROM your_table_name
    WHERE call_date = DATE_FORMAT(@input_date, '%d-%m-%Y') -- Match table's date format
),
last_month_data AS (
    -- Get same day last month data + monthly success rate + user's rank that month
    SELECT
        userid,
        calls_received AS cl_last_mon,
        calls_answered AS ans_last_mon,
        calls_rejected AS rej_last_mon,
        ROUND((calls_answered / calls_received)*100, 1) AS percentage_lm,
        RANK() OVER (ORDER BY (calls_answered / calls_received) DESC) AS position_last_month
    FROM your_table_name
    WHERE call_date = DATE_FORMAT(@last_month_date, '%d-%m-%Y')
),
last_year_data AS (
    -- Get same day last year data + yearly success rate + user's rank that year
    SELECT
        userid,
        calls_received AS cl_last_year,
        calls_answered AS ans_last_year,
        calls_rejected AS rej_last_year,
        ROUND((calls_answered / calls_received)*100, 1) AS percentage_ly,
        RANK() OVER (ORDER BY (calls_answered / calls_received) DESC) AS position_last_year
    FROM your_table_name
    WHERE call_date = DATE_FORMAT(@last_year_date, '%d-%m-%Y')
),
last_month_rank_map AS (
    -- Map rankings to success rates from last month (for diff calculation)
    SELECT
        position_last_month AS rank_num,
        percentage_lm AS rank_success
    FROM last_month_data
),
last_year_rank_map AS (
    -- Map rankings to success rates from last year
    SELECT
        position_last_year AS rank_num,
        percentage_ly AS rank_success
    FROM last_year_data
)

Step 2: Join Data & Calculate Final Metrics #

Now we'll join all datasets to combine the metrics and compute the percentage differences against same-ranked users from prior periods:

SELECT
    cd.userid,
    cd.cl_cur,
    cd.ans_cur,
    cd.rej_cur,
    CONCAT(cd.success_percentage, ' %') AS success_percentage,
    -- Difference between current success rate and last month's same-ranked user
    CONCAT(ROUND(cd.success_percentage - lmrm.rank_success, 1), '%') AS diff_percent,
    lmd.position_last_month,
    lmd.cl_last_mon,
    lmd.ans_last_mon,
    lmd.rej_last_mon,
    CONCAT(lmd.percentage_lm, '%') AS percentage_lm,
    lyd.cl_last_year,
    lyd.ans_last_year,
    lyd.rej_last_year,
    CONCAT(lyd.percentage_ly, '%') AS percentage_ly,
    -- Difference between current success rate and last year's same-ranked user
    CONCAT(ROUND(cd.success_percentage - lyrm.rank_success, 1), '%') AS diff_percent_ly,
    lyd.position_last_year
FROM current_data cd
LEFT JOIN last_month_data lmd ON cd.userid = lmd.userid
LEFT JOIN last_year_data lyd ON cd.userid = lyd.userid
LEFT JOIN last_month_rank_map lmrm ON cd.current_rank = lmrm.rank_num
LEFT JOIN last_year_rank_map lyrm ON cd.current_rank = lyrm.rank_num
-- Optional: Filter to only users with data across all three periods (matches your sample)
WHERE lmd.userid IS NOT NULL AND lyd.userid IS NOT NULL
ORDER BY cd.success_percentage DESC;

Key Notes for Adaptation #

• Database Compatibility: If using SQL Server instead of MySQL, adjust date functions:
  • Replace STR_TO_DATE with CONVERT(DATE, '${call_date}', 103)
  • Replace DATE_SUB with DATEADD(MONTH, -1, @input_date) / DATEADD(YEAR, -1, @input_date)
  • Replace DATE_FORMAT with FORMAT(@input_date, 'dd-MM-yyyy')
• Divide-by-Zero Protection: Add a check if calls_received could be 0:

  ROUND(IF(calls_received = 0, 0, (calls_answered / calls_received)*100), 1)
  

• Rank Adjustments: Use DENSE_RANK() instead of RANK() if you want consecutive rankings (no gaps for tied success rates), or ROW_NUMBER() for unique rankings even with ties.
• Report Tool Integration: Replace ${call_date} with your report tool's parameter syntax (e.g., @call_date in SSRS, {call_date} in Tableau).

内容的提问来源于stack exchange，提问作者Bommu