Mariadb – How to aggregate in the WHERE clause referencing the subquery from the FROM clause

aggregatejoin;mariadbselectwindow functions

I got the following query so far:

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=customers.ID
WHERE
  runtot < 2000 * c1.Priority / (SELECT SUM(Priority) FROM c1)
  OR rnum <= 1

It complains about a syntax error near SELECT SUM(Priority) FROM c1: neither c1, nor q1 table can be referenced there.

So the following doesn't work either for computing the sum:

(SELECT SUM(Priority) FROM customers WHERE ID IN q1.CustomerID)

What I'm trying to achieve is a sum of customers.Priority over just the customers selected in q1.

Could you please help me to fix the query if possible?

The DB is MariaDB v10.4.13 (you can also assume the latest version or even MySQL).

UPDATE: The following doesn't work either

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=customers.ID
WHERE
  runtot < 2000 * c1.Priority / (SUM(Priority) OVER (PARTITION BY q1.CustomerID))
  OR rnum <= 1

MariaDB responds with error 4015: Window function is allowed only in SELECT list and ORDER BY clause.

UPDATE2: Table creation and population

CREATE TABLE `multiqueue` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `CustomerID` BIGINT(20) NOT NULL,
    `Volume` INT(11) NOT NULL,
    `Content` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `PublishedTS` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `customers` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Priority` DOUBLE NOT NULL DEFAULT '1000',
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

INSERT INTO multiqueue VALUES
  (1, 1, 100, 'Content1', NULL),
  (2, 1, 200, 'Content2', NULL),
  (3, 1, 300, 'Content3', NULL),
  (4, 1, 400, 'Content4', NULL),
  (5, 1, 500, 'Content5', NULL),
  (6, 2, 100, 'Content6', NULL),
  (7, 2, 200, 'Content7', NULL),
  (8, 2, 300, 'Content8', NULL),
  (9, 2, 400, 'Content9', NULL),
  (10, 2, 500, 'Content10', NULL),
  (11, 1, 600, 'Content11', NULL)

INSERT INTO customers VALUES
  (1, 1000),
  (2, 500),
  (3, 100000)

I would like that the query produces approximately twice as much volume for customer #1 as for customer #2, and the total volume over both customers is about 2000.

UPDATE3: for the above inputs, the output can be:

ID    | Content   | Volume | CustomerID | runtot
------------------------------------------------
1     | Content1  | 100    | 1          | 100
2     | Content2  | 200    | 1          | 300
3     | Content3  | 300    | 1          | 600
4     | Content4  | 400    | 1          | 1000
6     | Content6  | 100    | 2          | 100
7     | Content7  | 200    | 2          | 300
8     | Content8  | 300    | 2          | 600

As we can see, the query must have selected a total volume of 1600 over both customers, approximately maintaining the proportion 1000:500 while keeping the total volume below 2000 if possible when selecting at least one row for every customer.

Best Answer

Your own solution might be suboptimal but I am not sure there is a way to solve the problem elegantly and/or efficiently without at least some redundancy. I managed to avoid hitting the customers table more than once, but I still had to reference multiqueue twice. This is the query I ended up with:

SELECT
  q.ID,
  q.Content,
  q.Volume,
  q.CustomerID,
  q.runtot
FROM
  (
    SELECT
      ID, Content, Volume, CustomerID,
      SUM(Volume)  OVER (PARTITION BY CustomerID ORDER BY ID ASC) AS runtot,
      ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID ASC) AS rnum
    FROM
      multiqueue
    WHERE
      PublishedTS IS NULL
  ) AS q
  INNER JOIN
  (
    SELECT
      ci.ID, ci.Priority,
      SUM(ci.Priority) OVER () AS TotalPriority
    FROM
      customers AS ci
    WHERE
      EXISTS
      (
        SELECT * FROM multiqueue AS qi
        WHERE qi.PublishedTS IS NULL AND qi.CustomerID = ci.ID
      )
  ) AS c ON q.CustomerID = c.ID
WHERE
  q.runtot < 2000 * c.Priority / c.TotalPriority
  OR q.rnum <= 1
;

Basically, the join to customers is replaced with a join to a nested select from customers that incorporates both the SUM(Priority) calculation and the EXISTS check. The check makes sure only the customers represented in multiqueue are returned, and as a result, the Priority total is calculated across those customers only.

The rest of the logic is, I hope, the same as in your own query.

You can test my solution in this demo at dbfiddle logodb<>fiddle.uk.