Mysql – How to write a query to get wine pairing for a pizza based on pairing weight

greatest-n-per-groupMySQLmysql-8.0

I am working on a query to pair drinks to pizzas.

Currently I am able to show the pizza_name, beverage and the pairing_weight being the amount of toppings on a pizza that the beverage pairs with. From here, I am looking to just output a mapping of distinct pizza name to beverage.

I am unsure how to format the query to just return the top result per pizza based on pairing_weight.

mysql> select pizza_name, beverage, COUNT(beverage) as pairing_weight
    -> from pizza_production
    ->     JOIN pizza_restrictions pr on
    ->         pizza_production.topping_name = pr.topping_name
    ->     JOIN beverage_pairing_notes on
    ->         restriction = pairing
    ->     GROUP BY pizza_name, beverage
    ->     ORDER BY pizza_name, pairing_weight DESC;
+--------------+--------------------+----------------+
| pizza_name   | beverage           | pairing_weight |
+--------------+--------------------+----------------+
| Grand Padano | Prosecco           |              2 |
| Grand Padano | Champagne          |              2 |
| Grand Padano | Riesling           |              2 |
| Grand Padano | Cava               |              2 |
| Grand Padano | Pinot Noir         |              1 |
| Grand Padano | Carlsberg beer     |              1 |
| Grand Padano | Zinfandel Rosé     |              1 |
| Grand Padano | Chardonnay         |              1 |
| Grand Padano | Chenin Blanc       |              1 |
| Grand Padano | Gewürztraminer     |              1 |
| Grand Padano | Guinness beer      |              1 |
| Grand Padano | Pinot Grigio       |              1 |
| Grand Padano | Heineken beer      |              1 |
| Grand Padano | Irn Bru            |              1 |
| Grand Padano | Malbec             |              1 |
| Grand Padano | Muscat Blanc       |              1 |
| new york     | Riesling           |              2 |
| new york     | Champagne          |              2 |
| new york     | Prosecco           |              2 |
| new york     | Pinot Noir         |              2 |
| new york     | Malbec             |              2 |
| new york     | Syrah              |              1 |
| new york     | Cava               |              1 |
| new york     | Cabernet Sauvignon |              1 |
| new york     | Chardonnay         |              1 |
| new york     | Carlsberg beer     |              1 |
| new york     | Chenin Blanc       |              1 |
| new york     | Gewürztraminer     |              1 |
| new york     | Guinness beer      |              1 |
| new york     | Muscat Blanc       |              1 |
| new york     | Heineken beer      |              1 |
| pepperoni    | Pinot Noir         |              3 |
| pepperoni    | Malbec             |              3 |
| pepperoni    | Muscat Blanc       |              2 |
| pepperoni    | Heineken beer      |              2 |
| pepperoni    | Guinness beer      |              2 |
| pepperoni    | Gewürztraminer     |              2 |
| pepperoni    | Chenin Blanc       |              2 |
| pepperoni    | Riesling           |              2 |
| pepperoni    | Carlsberg beer     |              2 |
| pepperoni    | Cabernet Sauvignon |              2 |
| pepperoni    | Chardonnay         |              1 |
| pepperoni    | Champagne          |              1 |
| pepperoni    | Prosecco           |              1 |
| pepperoni    | Cava               |              1 |
| pepperoni    | Rioja              |              1 |
| pepperoni    | Syrah              |              1 |
| pepperoni    | Zinfandel Rosé     |              1 |
| vegetarian   | Diet Coke          |              2 |
| vegetarian   | Prosecco           |              1 |
| vegetarian   | Champagne          |              1 |
| vegetarian   | Riesling           |              1 |
+--------------+--------------------+----------------+
52 rows in set (0.01 sec)

An example of desired output would be

+--------------+--------------------+
| pizza_name   | beverage           |
+--------------+--------------------+
| pepperoni    | Pinot Noir         |
| new york     | Riesling           |
| Grand Padano | Prosecco           |
| vegetarian   | Diet Coke          |
+--------------+--------------------+

Is there a way to pull this data from what I already have in the output of the query above?

Version:

mysql> status -------------- /usr/local/mysql/bin/mysql  Ver 8.0.23 for macos10.15 on x86_64 (MySQL Community Server - GPL)

If the pairing_weight is the same for multiple beverages per pizza then a choice of any of them is fine, it can be totally random if needed or just the first one the query displays with the highest weight.

Best Answer

As a classic Greatest N per group problem, this is easily solvable using the ROW_NUMBER() function:

SELECT
  pizza_name, beverage
FROM
  (
    select
        pizza_name, beverage,
        ROW_NUMBER() OVER (
          PARTITION BY pizza_name
          ORDER BY COUNT(beverage) DESC
        ) as pizza_beverage_ranking
    from pizza_production
        JOIN pizza_restrictions pr on
            pizza_production.topping_name = pr.topping_name
        JOIN beverage_pairing_notes on
            restriction = pairing
        GROUP BY pizza_name, beverage
  ) AS derived
WHERE
  pizza_beverage_ranking = 1
;

As specified in the ORDER BY clause of the ROW_NUMBER() function, the rankings are assigned in the descending order of COUNT(beverage). Beyond that, however, the order is arbitrary, and when two or more rows have the same weight, which one gets lower or higher ranking is going to be up to the server. You can add more criteria after COUNT(beverage) DESC if you want the order to be more specific and, consequently, the results more predictable. Apart from sorting, there's also the PARTITION BY clause, which additionally specifies that the enumeration be done separately for every group of rows sharing the same pizza_name value.

Once the row numbers are assigned, you can filter on them, as you can see done in the outer SELECT's WHERE clause.

_{As a side note, please consider always to qualify columns with table names or aliases (like pizza_production.pizza_name instead of pizza_name, for example) where the query involves more than one table. While the server can certainly resolve unqualified columns if there are no conflicts, it is easier for a human to read and understand a query if the columns are unambiguously qualified, particularly when the reader is not very familiar with the schema. This is as true for queries that you offer for study to an unknown audience, as it is for queries you have once written yourself and forgotten but have to go back to for some reason or other.}

Related Solutions

Mysql – How to write this query

This is best done by LATERAL JOINs or if it is not available window functions. MySQL unfortunately doesn't support either but the latter is relatively easy to be mimicked.

> set @rownum=0;
> set @grp_lag=null; 
> select id, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id  
from message 
order by grp_id, created_at desc;

+-------+--------+------------------+
| id    | rownum | @grp_lag:=grp_id |
+-------+--------+------------------+
| 50008 |      1 |                1 |
| 50007 |      2 |                1 |
| 50001 |      3 |                1 |
| 50004 |      4 |                1 |
| 50002 |      1 |                2 |
| 50005 |      2 |                2 |
| 50003 |      1 |                3 |
| 50006 |      1 |                6 |
+-------+--------+------------------+
8 rows in set (0.00 sec)

After having this you can have the final query something like this:

-- Set initial values for mimicing the window function
set @rownum=0;
set @grp_lag=null; 

-- Query every message where rownum < per group limit
select * from (
    -- subquery providing rownumber for every group order by the created_at column
    select *, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id 
    from message 
    where grp_id in (1,2)
    -- Futher filtering for optimisation if possible
    order by grp_id, created_at desc
) numbered
where rownum < 50;

You can wrap this into a procedure to execute it easier. Please keep in mind, set calls also return and they need to be in the same session (yet another reason for a stored procedure).

> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)

The query above will fetch every row where grp_id matches the in condition and generate the row number for each per group. So in case of large number of messages further optimisations are necessary. For example you can heuristically filter by created_at > [estimated date for having at least 50 message].

MySQL ORDER BY columns across multiple joined tables

I see "over-normalization" as the main problem.

The focus for this query seems to be on "language", yet that is the last thing checked.

For one attempt at a solution, let's rearrange the schema to these two tables:

CREATE TABLE restaurant_attributes (
  restaurant_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rating INT NOT NULL,
  PRIMARY KEY (restaurant_id),
);

CREATE TABLE restaurants_by_lang (
  restaurant_id INT UNSIGNED NOT NULL,
  language VARCHAR(5) NOT NULL CHARACTER SET ascii,  -- see note
  name VARCHAR(255) NOT NULL,
  location VARCHAR(255) NOT NULL,
  PRIMARY KEY(language, restaurant_id),
  INDEX (language, location, name),  -- perfect for the query
  INDEX (name),
  INDEX (location)
);

Now the query is simply:

SELECT location, name
    FROM restaurants_by_lang
    WHERE language = 'en'
    ORDER BY location, name
    LIMIT 0, 50;

This schema will even allow for efficient "pagination", which will be your next problem. (See here.)

I will suggest that if you have a million restaurants, listing the first 50 by location is a next-to-useless UI design. I recommend re-thinking the need for the query. For example, instead of paginating through the entire list, I suggest some form of drill-down such as country to state to city/area to restaurants. It will be a lot faster for the user to find Zahir's in Zimbabwe.

But... Since I can't see the rest of your SELECTs, I don't know what other things I may have made worse for some of them.

I picked a 5-char language based on a standard for such. Do not use 255 unless you really need such.

Testing

When testing with less data than you will eventually have, this technique is often handy...

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

Then look at the numbers. A number that approximates the number of rows in the table (or some multiple thereof) indicates a table scan(s). Some number that approximates the LIMIT value indicates that the query could whittle down the task efficiently.

I claim you will see '50' with my approach, and at least 7K (20K?) from your approach.

Note: Page 4 (with LIMIT 150, 50) would say 200 for mine; yours will not change. With the technique in my pagination link, even page 4 will be only 50.

Each temp table (due to GROUP BY and/or ORDER BY) will show Handler_write indicating the number of rows (times the number of tmp tables).

Best Answer

Related Solutions

Mysql – How to write this query

MySQL ORDER BY columns across multiple joined tables

Related Question