How to return cumulative values ​​to 2 dimensions using Python Cubes?

Question

Situation

Using Python 3, Django 1.9, Cubes 1.1, and Postgres 9.5.
This is my data in graphical form:

The same in text format:

Save table

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

Product table

------------------------------
| 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       |
|----------------------------|

Sales table

----------------------------------------
| 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    |
|--------------------------------------|

Relationship:

• Sales owned by the store.
• Sales owned by Product
• The store has a lot of sales.
• The product has a lot of sales.

What i want to achieve

I want to use cubes to display a paginated display as follows:

For stores S1-S3:

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

Please note the following:

• Despite the fact that there were no sales records in the Sucer 12 store in the S3 store, I displayed 0 instead of zero or none.
• I want to be able to sort by store, for example, in descending order, S3.
• SUM , .
• .

, :

"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 ....                         |
|---------------------------------------------|

, , , .

, ?

, , ?

Update

. , , , , 2 . .: https://en.wikipedia.org/wiki/OLAP_cube#Operations

Cubes GitHub, .

Answer 1

SQL-, crosstab() tablefunc pivot . , . crosstab(), :

• PostgreSQL

"extra" crosstab():

• Tablefunc

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 last:

  • PostgreSQL asc, null ?

• crosstab() :

  • CASE GROUP BY

---

4. .

. LIMIT OFFSET:

MATERIALIZED VIEW, . , MV .

, :

• SQL 'WHERE (col1, col2) < (val1, val2) '
• OFFSET