Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Browse files

Merge remote-tracking branch 'acoustid-api/live'

  • Loading branch information...
commit 1ce2275905909ae91b699ec5e5395256545c46c6 2 parents a2f3e8b + 45d9526
Lukáš Lalinský authored
Showing with 2 additions and 49 deletions.
  1. +2 −49 sql/CollectStats.sql
51 sql/CollectStats.sql
View
@@ -40,18 +40,14 @@ INSERT INTO stats (name, value) VALUES ('track.all', (
SELECT count(*) FROM track
));
-SELECT update_account_stats();
+--SELECT update_account_stats();
INSERT INTO stats (name, value) VALUES ('submission.all', (
SELECT sum(submission_count) FROM account
));
-INSERT INTO stats (name, value) VALUES ('submission.unhandled', (
- SELECT count(*) FROM submission WHERE not handled
-));
-
INSERT INTO stats (name, value) VALUES ('account.active', (
- SELECT count(*) FROM account WHERE submission_count > 0
+ SELECT count(DISTINCT account_id) FROM source
));
SELECT track_count, count(*) mbid_count
@@ -104,39 +100,6 @@ SELECT track_count, count(*) puid_count
) a
GROUP BY track_count ORDER BY track_count;
-INSERT INTO stats (name, value) VALUES ('puid.0tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=0), 0));
-INSERT INTO stats (name, value) VALUES ('puid.1tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=1), 0));
-INSERT INTO stats (name, value) VALUES ('puid.2tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=2), 0));
-INSERT INTO stats (name, value) VALUES ('puid.3tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=3), 0));
-INSERT INTO stats (name, value) VALUES ('puid.4tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=4), 0));
-INSERT INTO stats (name, value) VALUES ('puid.5tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=5), 0));
-INSERT INTO stats (name, value) VALUES ('puid.6tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=6), 0));
-INSERT INTO stats (name, value) VALUES ('puid.7tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=7), 0));
-INSERT INTO stats (name, value) VALUES ('puid.8tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=8), 0));
-INSERT INTO stats (name, value) VALUES ('puid.9tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count=9), 0));
-INSERT INTO stats (name, value) VALUES ('puid.10tracks', coalesce((SELECT sum(puid_count) FROM tmp_puid_tracks WHERE track_count>=10), 0));
-
-SELECT puid_count, count(*) track_count
- INTO TEMP TABLE tmp_track_puids
- FROM (
- SELECT count(tm.puid) puid_count
- FROM track t LEFT JOIN track_puid tm ON t.id=tm.track_id
- GROUP BY t.id
- ) a
- GROUP BY puid_count ORDER BY puid_count;
-
-INSERT INTO stats (name, value) VALUES ('track.0puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=0), 0));
-INSERT INTO stats (name, value) VALUES ('track.1puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=1), 0));
-INSERT INTO stats (name, value) VALUES ('track.2puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=2), 0));
-INSERT INTO stats (name, value) VALUES ('track.3puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=3), 0));
-INSERT INTO stats (name, value) VALUES ('track.4puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=4), 0));
-INSERT INTO stats (name, value) VALUES ('track.5puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=5), 0));
-INSERT INTO stats (name, value) VALUES ('track.6puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=6), 0));
-INSERT INTO stats (name, value) VALUES ('track.7puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=7), 0));
-INSERT INTO stats (name, value) VALUES ('track.8puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=8), 0));
-INSERT INTO stats (name, value) VALUES ('track.9puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count=9), 0));
-INSERT INTO stats (name, value) VALUES ('track.10puids', coalesce((SELECT sum(track_count) FROM tmp_track_puids WHERE puid_count>=10), 0));
-
INSERT INTO stats (name, value) VALUES ('mbid.onlyacoustid', (
select count(distinct mbid) from track_mbid tm join musicbrainz.recording r on r.gid=tm.mbid left join musicbrainz.recording_puid rp on rp.recording=r.id where rp.recording is null
));
@@ -149,14 +112,4 @@ INSERT INTO stats (name, value) VALUES ('mbid.both', (
select count(distinct r.gid) from musicbrainz.recording r join musicbrainz.recording_puid rp on rp.recording=r.id join track_mbid tm on tm.mbid=r.gid
));
-DELETE FROM stats_top_accounts;
-INSERT INTO stats_top_accounts (account_id, count)
- SELECT account_id, count(*) FROM (
- SELECT so.account_id
- FROM submission su
- JOIN source so ON su.source_id=so.id
- WHERE su.created > now() - INTERVAL '14' DAY
- ) a
- GROUP BY account_id;
-
COMMIT;
Please sign in to comment.
Something went wrong with that request. Please try again.