Amarok/Archives/Proposals/Statistics: Difference between revisions

From KDE Community Wiki
(Created page with "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 listene...")
 
m (Text replace - "\[\[Category:.*\]\]" to "")
 
(4 intermediate revisions by one other user not shown)
Line 1: Line 1:
It would be nice to see some (fairly) random statistics in amaroK, based on one's music collection, time listening, et cetera
It would be nice to see some (fairly) random statistics in amaroK, based on one's music collection, time listening, et cetera


Line 9: Line 10:


And it would also be nice to do the above with the TIME of the songs
And it would also be nice to do the above with the TIME of the songs
For example
For example
Total time of songs from album/Time of all songs
* Total time of songs from album/Time of all songs
Total time of songs by artist/Time of all songs
* Total time of songs by artist/Time of all songs
Total time of songs in genre/Time of all song
* Total time of songs in genre/Time of all song


Another feature that would be nice
Another feature that would be nice
(Number of songs in genre A)/(nubmer of songs in Genre B)
* (Number of songs in genre A)/(nubmer of songs in Genre B)
Example output
* Example output
User listens to Genre A twice as much as Genre B
* User listens to Genre A twice as much as Genre B
The same could also be done for artists and albums.
* The same could also be done for artists and albums.


== SQL Query Collection ==
== SQL Query Collection ==

Latest revision as of 11:00, 14 December 2012

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