How to limit rows per month in SQL

Asked

Viewed 90 times

3

I have a list of products purchased with date and price, I wanted to organize in a query the ten most purchased of each month, type:

mes     | nome   | total
janeiro | arroz  | 100
janeiro | feijão | 87
... até a décima
fevereiro |feijão | 110
... e assim vai dentro de um intervalo como seis meses

table q have something like

id | id_cliente | nome   | data_compra | preco_da_compra
32 | 12345678   | feijão | 2021-01-12  | 35 
33 | 121434366  | arroz  | 2021-01-11  | 25
34 | 223321213  | arroz  | 2021-01-11  | 22

I have the following query

SELECT
    DATE_FORMAT(data_compra, "%b") as mes,
    nome,
    (select count(distinct nome) from compras where compras.nome = nome) as total
 WHERE
    DATE_FORMAT(data_compra, "%b")= "Jan" 
ORDER BY total DESC
LIMIT 10;

but this query takes only January the first ten, I wanted to get this same query month by month without having to manually change the date, in an interval like January to March.

sql select-sql mariadb select-limit

  • some details like: which is sgdb (mysql, postgres)? and what means "most purchased", is a count of the quantity by name, or by the sum of the purchase price?

  • 1

    In this case you would have to create a precedent, do one while a month as desired and go inserting this data into a table to query it later, or simply copy this select with the filter of the month and repeat it several times with Union all.

  • You could have informed the database structure and given some data that could help with the mass test. Here’s how to create a Minimum, Complete and Verifiable Example

1 answer

1

You can solve using ROW_NUMBER together with WITH:

WITH totais AS (
  SELECT DATE_FORMAT(c.data_compra, "%b") AS mes,
         c.nome,
         COUNT(c.nome) AS total
    FROM compras c
   GROUP BY DATE_FORMAT(c.data_compra, "%b"), c.nome
), delimitadores AS (
  SELECT t.*,
         ROW_NUMBER(PARTITION BY t.mes ORDER BY t.total DESC) AS posicao
    FROM totais t
)
SELECT d.mes,
       d.nome,
       d.total
  FROM delimitadores d
 WHERE d.posicao <= 10

ROW_NUMBER

ROW_NUMBER() is a window Function that displays the number of a Given Row, Starting at one and following the ORDER BY Sequence of the window Function, with identical values Receiving Different Row Numbers.

In free translation:

ROW_NUMBER () is a window function that displays the number of a given line, starting at one and following the sequence ORDER BY of the window function, with identical values receiving different line numbers.

WITH

The WITH keyword signifies a Common Table Expression (CTE). It Allows you to refer to a subquery Expression Many times in a query, as if having a Temporary table that only exists for the Duration of a query.

In free translation:

The keyword WITH references a Common Table Expression (CTE). It allows you to refer to an undersold expression often in a query, as if you had a temporary table that exists only during the query.

Note: Since you did not provide the table structure and a minimum mass of test data, the query may contain errors of execution.

Browser other questions tagged sql select-sql mariadb select-limit

You are not signed in. Login or sign up in order to post.