Problem with SQLite query

Question

Problem with SQLite query

Navigation

#1 by (2 votes)

2

the query I want to make is about the selection of a song with certain specifications, to make a playlist, it is generated as the songs are played, the query is something like selecting the next song. In the table I have a logical data like, "reproduced" where it turns to true when it is played and "ultimareproduccion" where the DATETIME is placed at the time of playback. Here is the schema of the database:

Table albums

CREATE TABLE "albums" ( 'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'nombre' TEXT );

Music table

CREATE TABLE "musica" ( 'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 'nombreyruta' TEXT NOT NULL, 'anulado' NUMERIC NOT NULL DEFAULT 0, 'album' INTEGER, 'reproducido' INTEGER NOT NULL DEFAULT 0, 'ultimareproduccion' TEXT );

Reproduction table

CREATE TABLE "reproduccion" ( 'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 'idalbum' INTEGER NOT NULL DEFAULT 0, 'habilitado' INTEGER NOT NULL DEFAULT 1 )

I would need that in the selection of the next topic, it is based on a SELECT query, that it chooses the next topic to be reproduced in the following way, that it has the data REPRODUCTION = 0, and that it avoids selecting the ones that have been reproduced recently. time, and also, that within this in a percentage you can perform a Ramdom, or random selection. An example, the theme of reproduction ordered from highest to lowest in "ULTIMAREPRODUCCION" is requested and that 40% of these older ones can be randomly selected. The main idea is that the playlist is not repetitive, that is, that the list is not always the same, and that the songs are not heard one after the other.

Here I leave the selection that I have to previous ones, what it does is select all the themes that can be chosen for its reproduction:

SELECT musica.id from musica inner join reproduccion on musica.album = reproduccion.idalbum  WHERE musica.reproducido = 0;

As you can see, the reproduction is not carried in the database, but it is done when you ask with a SELECT which is the next song to play. what would it take to solve this question!

I hope you can help me thank you very much

sql sqllite

asked by Bernardo Harreguy 20.11.2017 в 13:34

source

1 answer

problems with the primary key ASP.net pass onclick values to another page

score 2 · Accepted Answer

To calculate 40% of the songs stored in the musica table we can use COUNT(*) and multiply it by the desired value (0.4 to 40%):

SELECT CAST(COUNT(*) * 0.4 AS int) FROM musica

Since the result is a floating point number, you have to convert it to an integer with a CAST .

That result can be used in the SQL clause LIMIT :

SELECT *
FROM musica m
ORDER BY ultimareproduccion DESC
LIMIT (
  SELECT CAST(COUNT(*) * 0.4 AS int) + 1 FROM musica
)

I have added 1 to the 40% calculation to round up and avoid that no record is obtained when it is 0.

Now we can use ORDER RANDOM() to clutter that 40% of records obtained.

The random() function returns random values for each record previously selected, so the records will be reordered accordingly. to that random value.

Finally, we only need to keep one of them with LIMIT 1 :

SELECT c.*
FROM (
  SELECT *
  FROM musica m
  ORDER BY ultimareproduccion DESC
  LIMIT (
    SELECT CAST(COUNT(*) * 0.4 AS int) + 1 FROM musica
  )
) c
ORDER BY RANDOM()
LIMIT 1

Online example of the proposed SQL: link