如何转置含子查询与计数的SQL查询结果以绘制直方图？

Hey there! I see you need to transpose your SQL query results to make them histogram-friendly—turning that single row of metrics into individual rows for each metric name. Let's fix that up for you.

Here's the adjusted query that will output each metric as a separate row, with two columns: metric_name (your desired row labels: new_objective, new_milestone, main_objective, main_milestone, main_kr) and count (the corresponding value):

SELECT 'new_objective' AS metric_name, 
       (SELECT count(label) 
        FROM "roads-fe-db"."track" 
        WHERE key IN ('newarea_option_clicked') 
          AND label LIKE '%option.[object_object],_objective%' 
        GROUP BY label 
        ORDER BY count(*) desc 
        LIMIT 1) AS count
UNION ALL
SELECT 'new_milestone' AS metric_name,
       (SELECT count(label) 
        FROM "roads-fe-db"."track" 
        WHERE key IN ('newarea_option_clicked') 
          AND label LIKE '%option.[object_object],_milestones%' 
        GROUP BY label 
        ORDER BY count(*) desc 
        LIMIT 1) AS count
UNION ALL
SELECT 'main_objective' AS metric_name,
       (SELECT count(label) 
        FROM "roads-fe-db"."track" 
        WHERE key IN ('okrboard_newobjective_clicked') 
        GROUP BY label 
        ORDER BY count(*) desc 
        LIMIT 1) AS count
UNION ALL
SELECT 'main_milestone' AS metric_name,
       (SELECT count(label) 
        FROM "roads-fe-db"."track" 
        WHERE key IN ('milestoneboard_newmilestone_clicked')) AS count
UNION ALL
SELECT 'main_kr' AS metric_name,
       (SELECT count(label)
        FROM "roads-fe-db"."track" 
        WHERE label LIKE '%+_new_key_result%') AS count;

Key Notes on the Adjustments: #

• Fixed a duplicate main_objective alias in your original query—mapped the first subquery to new_objective to match its actual purpose.
• Added LIMIT 1 to subqueries using GROUP BY label: Since grouping by label can return multiple rows, this ensures we grab the top count result (as your original query used ORDER BY count(*) desc to prioritize the highest value).
• Used UNION ALL to combine each metric's result into a row-based dataset: This structure is perfect for histograms—you’ll use metric_name for the x-axis and count for the y-axis.

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