Как вернуть агрегированные значения по двум измерениям, используя кубы Python?

postgresql data-warehouse olap olap-cube cubes

386 просмотра

1 ответ

4876 Репутация автора

ситуация

Использование Python 3, Django 1.9, Cubes 1.1 и Postgres 9.5.
Это мои данные в графической форме:

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

То же самое в текстовом формате:

Магазинный стол

------------------------------
| id  | code | address       |
|-----|------|---------------|
| 1   | S1   | Kings Row     |
| 2   | S2   | Queens Street |
| 3   | S3   | Jacks Place   |
| 4   | S4   | Diamonds Alley|
| 5   | S5   | Hearts Road   |
------------------------------

Таблица продуктов

------------------------------
| id  | code | name          |
|-----|------|---------------|
| 1   | P1   | Saucer 12     |
| 2   | P2   | Plate 15      |
| 3   | P3   | Saucer 13     |
| 4   | P4   | Saucer 14     |
| 5   | P5   | Plate 16      |
|  and many more ....        |
|1000 |P1000 | Bowl 25       |
|----------------------------|

Таблица продаж

----------------------------------------
| id  | product_id | store_id | amount |
|-----|------------|----------|--------|
| 1   | 1          | 1        |7.05    |
| 2   | 1          | 2        |9.00    |
| 3   | 2          | 3        |1.00    |
| 4   | 2          | 3        |1.00    |
| 5   | 2          | 5        |1.00    |
|  and many more ....                  |
| 1000| 20         | 4        |1.00    |
|--------------------------------------|

Отношения:

  1. Продажа принадлежит магазину
  2. Продажи принадлежат продукту
  3. Магазин имеет много продаж
  4. Продукт имеет много продаж

Чего я хочу достичь

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

Учитывая магазины S1-S3:

-------------------------
| product | S1 | S2 | S3 |
|---------|----|----|----|
|Saucer 12|7.05|9   | 0  |
|Plate 15 |0   |0   | 2  |
|  and many more ....    |
|------------------------|

Обратите внимание на следующее:

  1. Несмотря на то, что в магазине S3 не было записей о продажах для Saucer 12, я отображал 0 вместо нуля или ни одного.
  2. Я хочу быть в состоянии сделать сортировку по магазину, скажем, в порядке убывания, S3.
  3. Ячейки показывают сумму SUM этого конкретного продукта, потраченного в этом конкретном магазине.
  4. Я также хочу иметь нумерацию страниц.

Что я пробовал

Это конфигурация, которую я использовал:

"cubes": [
    {
        "name": "sales",
        "dimensions": ["product", "store"],
        "joins": [
            {"master":"product_id", "detail":"product.id"},
            {"master":"store_id", "detail":"store.id"}
        ]
    }
],
"dimensions": [
    { "name": "product", "attributes": ["code", "name"] },
    { "name": "store", "attributes": ["code", "address"] }
]

Это код, который я использовал:

 result = browser.aggregate(drilldown=['Store','Product'],
                               order=[("Product.name","asc"), ("Store.name","desc"), ("total_products_sale", "desc")])

Я не получил то, что хочу.
Я получил это так:

----------------------------------------------
| product_id | store_id | total_products_sale |
|------------|----------|---------------------|
| 1          | 1        |       7.05          |
| 1          | 2        |       9             |
| 2          | 3        |       2.00          |
|  and many more ....                         |
|---------------------------------------------|

это целая таблица без нумерации страниц, и если товары не продаются в этом магазине, она не будет отображаться как ноль.

Мой вопрос

Как получить то, что я хочу?

Нужно ли создавать еще одну таблицу данных, которая объединяет все данные по магазинам и продуктам, прежде чем использовать кубы для выполнения запроса?

Обновить

Я прочитал больше. Я понял, что то, что я хочу, называется нарезанием кубиками, так как мне нужно было пройти через 2 измерения. Смотрите: https://en.wikipedia.org/wiki/OLAP_cube#Operations

Перекрестная публикация на кубах GitHub вопросов, чтобы получить больше внимания.

Автор: Kim Stacks Источник Размещён: 15.07.2016 07:00

Ответы (1)


1 плюс

387661 Репутация автора

Это чисто SQL-решение, использующее crosstab()дополнительный модуль tablefunc для поворота агрегированных данных. Обычно он работает лучше, чем любая альтернатива на стороне клиента. Если вы не знакомы crosstab(), сначала прочтите это :

И это о «лишнем» столбце в crosstab()выводе:

SELECT product_id, product
     , COALESCE(s1, 0) AS s1               --  1. ... displayed 0 instead of null
     , COALESCE(s2, 0) AS s2
     , COALESCE(s3, 0) AS s3
     , COALESCE(s4, 0) AS s4
     , COALESCE(s5, 0) AS s5
FROM   crosstab(
     'SELECT s.product_id, p.name, s.store_id, s.sum_amount
      FROM   product p
      JOIN  (
         SELECT product_id, store_id
              , sum(amount) AS sum_amount  -- 3. SUM total of product spent in store
         FROM   sales
         GROUP  BY product_id, store_id
         ) s ON p.id = s.product_id
      ORDER  BY s.product_id, s.store_id;'
   , 'VALUES (1),(2),(3),(4),(5)'          -- desired store_id's
   ) AS ct (product_id int, product text   -- "extra" column
          , s1 numeric, s2 numeric, s3 numeric, s4 numeric, s5 numeric)
ORDER  BY s3 DESC;                         -- 2. ... descending order for S3

Выдает желаемый результат точно (плюс product_id).

Для того, чтобы включать в себя продукты , которые никогда не были проданы заменить [INNER] JOINс LEFT [OUTER] JOIN.

SQL Fiddle с базовым запросом.
Модуль tablefunc не установлен в sqlfiddle.

Основные моменты

  • Прочитайте основное объяснение в справочном ответеcrosstab() .

  • Я в том числе и product_idпотому, что product.nameвряд ли уникален. В противном случае это может привести к скрытым ошибкам, связывающим два разных продукта.

  • Вам не нужна storeтаблица в запросе, если ссылочная целостность гарантирована.

  • ORDER BY s3 DESCработает, потому что s3ссылается на выходной столбец, где значения NULL были заменены на COALESCE. В противном случае нам нужно DESC NULLS LASTотсортировать значения NULL последними:

  • Для построения crosstab()запросов динамически учитывайте:


  1. Я также хочу иметь нумерацию страниц.

Этот последний пункт нечеткий. Простая нумерация страниц может быть с LIMITи OFFSET:

Я хотел бы рассмотреть MATERIALIZED VIEWрезультаты до нумерации страниц. Если у вас стабильный размер страницы, я бы добавил номера страниц в MV для простых и быстрых результатов.

Чтобы оптимизировать производительность для больших наборов результатов, рассмотрите:

Автор: Erwin Brandstetter Размещён: 24.07.2016 03:28
Вопросы из категории :
32x32