Amarok/Archives/Proposals/Statistics

From KDE Community Wiki
Revision as of 12:18, 9 December 2012 by Mayankmadan (talk | contribs)

It would be nice to see some (fairly) random statistics in amaroK, based on one's music collection, time listening, et cetera

For example, statistics of how much of a listener's music is of a certain genre (for our purposes, we'll use Rock) Have the statistics code gather up all the genre information in the database, which, for many songs, simply will not show up, but this IS editable by the user, so they can class their music as they see fit. Tally the total number of the genre then it's simple math Number In Genre/Total songs

The same can be done with percentage of artists, but it would be Number of songs by Artist/Total songs

And it would also be nice to do the above with the TIME of the songs For example

Total time of songs from album/Time of all songs

Total time of songs by artist/Time of all songs

Total time of songs in genre/Time of all song

Another feature that would be nice

(Number of songs in genre A)/(nubmer of songs in Genre B)

Example output

User listens to Genre A twice as much as Genre B

The same could also be done for artists and albums.

SQL Query Collection

These collection may be used in your IRC client, PHP scripts, on your command line or in other programs. Usage via command line:

dcop amarok collection query "[your query]"

Quantities

  • Artist count
SELECT COUNT(*) from artist
  • Song count
SELECT COUNT(title) from tags
  • Album Count
SELECT COUNT(*) from album
  • Played Songs
SELECT count(playcounter) from statistics

Statistics

  • Last song played on ...
SELECT DATE_FORMAT(FROM_UNIXTIME(MAX(accessdate)), '%d.%m.%Y') from statistics
  • First song played on ...
SELECT DATE_FORMAT(FROM_UNIXTIME(MIN(accessdate)), '%d.%m.%Y') from statistics
  • Average bitrate
SELECT ROUND(AVG(bitrate),0) from tags
  • Average play count
SELECT ROUND(AVG(playcounter),1) from statistics
  • Total play count
SELECT SUM(playcounter) from statistics
  • Songs skipped after an average of X percent
SELECT ROUND(AVG(percentage),1) from statistics
  • Total Time in minutes (s/60=m)
SELECT ROUND(SUM(length)/60,2) from tags
  • total time in hours (m / [60*60=3600] = h)
SELECT ROUND(SUM(length)/3600,2) from tags
  • total time in days (h / [3600*24=86400] = d)
SELECT ROUND(SUM(length)/86400,2) from tags
  • total time in years (d / [86400*365=31536000] = y)
SELECT ROUND(SUM(length)/31536000,2) from tags