Oracle SQL Query for Stacked Pareto Chart

У меня есть таблица Oracle, которая содержит данные, подобные следующему базовому примеру:

+--------+----------+
| SERIES | CATEGORY |
+--------+----------+
| green  | apple    |
| green  | pear     |
| green  | pear     |
| yellow | apple    |
| yellow | apple    |
| yellow | pear     |
| yellow | pear     |
| yellow | pear     |
| yellow | banana   |
| yellow | banana   |
| yellow | banana   |
| red    | apple    |
+--------+----------+

Я хотел бы создать Парето-подобный график этих данных, который должен выглядеть как сложенная диаграмма Парето,

Введите описание изображения здесь

Чтобы создать этот график, я хотел бы запустить SQL-запрос и получить следующие выходные данные:

+----------+--------+-------+
| CATEGORY | SERIES | COUNT |
+----------+--------+-------+
| pear     | green  |     2 |
| pear     | yellow |     3 |
| apple    | green  |     1 |
| apple    | yellow |     2 |
| apple    | red    |     1 |
| banana   | yellow |     3 |
+----------+--------+-------+

Фактическая таблица имеет миллионы записей, и в настоящее время требуется значительное время для запроса базы данных, поскольку текущая процедура, которую я использую, не очень эффективна:

Порядок категорий по количеству операций в каждой категории:

SELECT CATEGORY, COUNT(CATEGORY) FROM FRUIT GROUP BY CATEGORY ORDER BY COUNT(CATEGORY);

Затем для каждой категории I перечислите соответствующие ряды в порядке следования рядов:

SELECT SERIES, COUNT(SERIES) FROM FRUIT WHERE CATEGORY = [current category] GROUP BY SERIES ORDER BY SERIES;

Каков был бы наиболее эффективный способ запроса базы данных (предпочтительно одной инструкции SQL) для получения желаемого результата?

2 ответа

  1. Вы можете достичь желаемого результата путем группировки по обоим CATEGORYи SERIES:

    SELECT 
        CATEGORY, SERIES, COUNT(*) 
    FROM FRUIT 
    GROUP BY CATEGORY, SERIES 
    ORDER BY COUNT(*);
    

    ОБНОВЛЕНИЕ:

    Для того чтобы приказать итогом CATEGORYсперва и после этого зеленого цвета, желтый цвет, красный цвет, как раз как ваш предполагаемый выход:

    SELECT t1.*
    FROM (
        SELECT 
            CATEGORY, SERIES, COUNT(*) AS CNT
        FROM FRUIT 
        GROUP BY CATEGORY, SERIES 
    ) t1
    INNER JOIN (
        SELECT
            CATEGORY, COUNT(*) AS CNT
        FROM FRUIT
        GROUP BY CATEGORY
    ) t2
        ON t1.CATEGORY = t2.CATEGORY
    ORDER BY 
        t2.CNT DESC,
        CASE t1.SERIES
            WHEN 'green' THEN 1
            WHEN 'yellow' THEN 2
            WHEN 'red' THEN 3
        END
    
  2. Некоторые более короткие версии:

    select category, series, CntS 
    from (
      select  distinct count(category) over (partition by category) cntC,
       count(series)  over (partition by category, series ) cntS,
       category, series 
    from fruit   ) Tab
    order by CntC desc, cntS desc;