-1

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.

  • Use window version - WHERE runtot < 2000 * customers.Priority / SUM(Priority) OVER (q1.CustomerID), it seems. – Akina Jun 22 at 7:32
  • @Akina, I've edited the question to show how this fails too. – Serge Rogatch Jun 22 at 8:03
  • Create a fiddle or provide tables DDLs (as CREATE TABLE) and some sample data (as INSERT INTO). Show desired output for this data. – Akina Jun 22 at 8:06
  • @Akina, please, see the edited question. – Serge Rogatch Jun 22 at 8:27
  • Your queries refers to BlockToken and UrlCount columns which are absent in tables DDLs. PS. dbfiddle.uk/… – Akina Jun 22 at 8:45
1

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.

| improve this answer | |
  • Thanks for your answer. I've measured: your query yields the same performance as mine on production data, but this performance is bad: ~0.64 sec for 5744 records in multiqueue table and ~3 records in customers table. So the algorithm the DB uses is at least O(N*N) where N is the number of records in the multiqueue table. – Serge Rogatch Jun 23 at 16:19
0
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=c1.ID
WHERE
  runtot < 2000 * Priority / (SELECT SUM(Priority) FROM customers WHERE ID = CustomerID)
  OR rnum <= 1
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
 5 | Content5  |    500 |          1 |   1500
 6 | Content6  |    100 |          2 |    100
 7 | Content7  |    200 |          2 |    300
 8 | Content8  |    300 |          2 |    600
 9 | Content9  |    400 |          2 |   1000
10 | Content10 |    500 |          2 |   1500
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=c1.ID
WHERE
  runtot < 2000 * Priority / (SELECT SUM(Priority) FROM customers)
  OR rnum <= 1
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

db<>fiddle here

Depending what information you want, you can select the max(ID) for every customerID(GROUP BY)

| improve this answer | |
  • It's wrong to select into the denominator just the priority of a single customer. I was trying to select the sum of priorities of all customers who have an active row in the queue. – Serge Rogatch Jun 22 at 12:48
  • Then remove the WHERE Clause in the subselect, for me this makes more sense. – nbk Jun 22 at 12:51
  • Without the WHERE clause the subselect would aggregate over all the customers, including customers who do not have an active row (request) in the queue. There may be 100 customers, of them only 2 have simultaneous requests. If I aggregate the priority over all 100 customers, I would fetch a volume of ~20 instead of 2000 from the DB. – Serge Rogatch Jun 22 at 12:58
  • The frist query makes sense to me, you said no it has to be overall all, ok i changed teh qiey to do exactly that, but this diesn't m,any sense at all – nbk Jun 22 at 13:02
  • Perhaps it will be more clear if you look at the query that achieves the goal in a suboptimal way ( dba.stackexchange.com/a/269658/144167 ) and try to optimize. – Serge Rogatch Jun 22 at 16:27
0

Below is a query that achieves the goal, though it might be far suboptimal:

SELECT p1.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 p1
JOIN customers AS c1 ON p1.CustomerID=c1.ID
WHERE
  runtot < 2000 * c1.Priority / (
    SELECT SUM(Priority) FROM customers AS c2
    WHERE EXISTS (
      SELECT 1 FROM multiqueue AS p2
      WHERE p2.CustomerID=c2.ID
        AND p2.PublishedTS IS NULL
    )
  )
  OR rnum <= 1

Verified at dbfiddle.

| improve this answer | |
  • your query still doesn't work, and gives back the same result(after fixuig it). you hole change with thewhere clause doesn't add or remove anything.and is in my opinio not needed – nbk Jun 22 at 16:33
  • @nbk, I've fixed the query (it's hard to maintain a working query while moving from production to sandbox). To see the point, you can add customer #3 with Priority=100000 . Then with the first your query you would be always selecting 2000 of volume for each customer, and with your second query you would be always selecting just 1 row per customer #1 and #2. – Serge Rogatch Jun 22 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.