Hola gente, como andan? Espero que bien!

Me dieron un ejercicio de base de datos y la verdad es que no sé por donde encararlo, sí alguien me ayuda genial!

Tengo la tabla de pizzas con sus ingredientes y la tabla de ordenes (adjuntadas)

El ejercicio me pide imprimir una nueva pizza que tenga los 5 ingredientes más utilizados en los últimos 6 meses. Cómo hago ese contador de cada uno de los ingredientes utilizados dentro de un array? La verdad es que me mató! Gracias!

Les dejo el código para insertar datos en un mysql:


CREATE TABLE sys.orders(
order_id int NOT NULL,
customer_id int NULL,
pizza_id int NULL,
exclusions varchar(4) NULL,
extras varchar(4) NULL,
order_time datetime NULL);

INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(1, 101, 1, N'', N'', CAST(N'2021-02-01T18:05:02.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(2, 101, 1, N'', N'', CAST(N'2021-02-01T19:00:52.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(3, 102, 1, N'', N'', CAST(N'2021-03-02T23:51:23.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(3, 102, 2, N'', NULL, CAST(N'2021-03-02T23:51:23.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(4, 103, 1, N'4', N'', CAST(N'2021-05-04T13:23:46.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(4, 103, 2, N'4', N'', CAST(N'2021-05-04T13:23:46.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(5, 104, 1, N'null', N'1', CAST(N'2021-06-08T21:00:29.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(6, 101, 2, N'null', N'null', CAST(N'2021-06-08T21:03:13.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(7, 105, 2, N'null', N'1', CAST(N'2021-06-08T21:20:29.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(8, 102, 1, N'null', N'null', CAST(N'2021-07-09T23:54:33.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(9, 103, 1, N'4', N'1, 5', CAST(N'2021-08-10T11:22:59.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(10, 104, 1, N'null', N'null', CAST(N'2021-09-11T18:34:49.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(11, 115, 3, NULL, N'8', CAST(N'2021-09-11T19:45:29.000' AS DateTime)) ;

CREATE TABLE sys.pizza(
id int NOT NULL,
name varchar(50) NULL,
ingredients varchar(50) NULL);

INSERT sys.pizza (id, name, ingredients) VALUES (1, N'Carnivore', N'1, 2, 3, 4, 5, 6, 8, 10');
INSERT sys.pizza (id, name, ingredients) VALUES (2, N'Vegetarian', N'4, 6, 7, 9, 11, 12');
INSERT sys.pizza (id, name, ingredients) VALUES (3, N'Prosciutto e sql', N'11, 12, 13, 6') ;

Holii

Estaba viendo un poco el problema, y es un poco engorroso el tema de las tablas.
En primer lugar, veo un problema con el modelo: si bien quizás se haya querido desnormalizar por un probable alto volumen de registros en las tablas, los ingredientes se tratan como códigos pero no tienen su propia tabla. Aconsejaría crear la tabla Ingredientes que tenga código y descripción, por ejemplo. De esta forma podríamos tener un acceso más fácil a los mismos y relacionarlos más rápidamente con las pizzas mediante una tabla pizza-ingredientes, por ejemplo. Luego se puede hacer algo similar con las órdenes y el código quedará mucho más prolijo a la hora de consultarlo. Sin mencionar que el parseo de los ingredientes (que son varchar) en las tablas para poder separarlos y compararlos (deberían ser del tipo int) es otro lindo tema.

Besis

Hola Nico, buen día! Mala mía! Me habían pasado la tabla de Ingredientes pero cómo solo era una descripción no creí que iba a ser necesaria para el ejercicio, ya que con sabes los id's de los ingredientes bastaba. Pero ahí lo agregué.

Cómo encararías la resolución del ejercicio? O algún otro, alguna ayuda?

Gracias!

Buenas,

Conceptualmente ese modelo de datos está mal construido, ya que la relación entre ingredientes y pizzas es de Muchos a Muchos, por lo que necesitas una tabla intermedia para asociar cada los Ingredientes a las Pizzas, y luego poder armar la query.
Lo mismo ocurre con la seccion extras, deberia ir en una tabla distinta de los orders.

Necesitariamos tener en resumen las siguientes tablas:
1. Tabla de Orders (Id de Orden, Pizza Pedida, Cliente, Fecha, etc)
2. Tabla Maestra de Pizzas (Nombre de Pizza, cuando se creo, etc etc)
3. Tabla Maestra de Ingredientes (Id de Ingrediente, Desc, etc etc)
4. Tabla que Relaciona las Pizzas con sus Ingredientes (Id de Pizza, Id de Ingrediente, etc etc
5. Tabla de Extras (Id de Orden, Id de Ingrediente Extra, etc etc).

Si bien se puede trabajar con lo que tenes, en mySQL es dificil desagregar esa columna de Ingredientes en Filas (en GCP es bastante facil de hacer).

Pero si tuviesemos los ingredientes en filas y no en una columna, el SQL que habría que armar es tan simple como:

 

SELECT Ingrediente, CantidadVecesUtilizado, ROW_NUMBER() OVER (ORDER BY CantidadVecesUtilizado DESC) Ranking

 FROM (

SELECT Pizza.Ingrediente, COUNT(1) as CantidadVecesUtilizado FROM Orders 

JOIN Pizza ON Orders.Pizza_Id = Pizza.Id

WHERE Order_Time >= NOW() - INTERVAL '6 months'  #ULTIMOS 6 MESES

GROUP BY Pizza.Ingrediente

) rnk_Ingredientes

WHERE rnk < 6 #PRIMEROS 5

Esto asumiendo que en la tabla de Pizza, tengo 1 registro por ingrediente (sino usar una intermedia)

Cualquier

Perfecto, gracias chicos!

Entonces me queda claro que el modelo está mal armado y hay que normalizar una tabla de pizza e ingredientes, donde sea una fila por cada combinación!

Gracias y saludos!

Chicos!

Ya que estoy te pregunto por el otro enunciado que me pide el ejercicio:
- Generar en orden alfabético separado por comas una lista de ingredientes por cada pizza agregando 2x delante de cada ingrediente que requiere un extra en la pizza.

Por ejemplo:
Para order_id = 5 would sería: "2xBacon, BBQ Sauce, Beef, Cheese, Chicken, Mushrooms,
Pepperoni, Salami".

Acá cómo lo encararían ustedes?

Ya lo resolví gente, gracias!