Ember TV Library analysis query
#1
A developer I used to work with (S.D. 'The Codemaster') wrote this SQL query so that we could analyze the TV part of our Ember libraries. I made some small changes to it for the latest Ember schema.
I thought it might be of some use to other Ember users. I am using SQLiteSpy to access MyVideos17.emm. No support is provided, use it only if you have a bit of SQL experience!!

@DanCooper, this could be the foundation of a useful 'TV Library Overview' feature Smile

Code:
-- ## Check the quality of the TV shows
SELECT a.idShow, a.Title, a.status, a.Premiered, max(b.aired) as 'LastAired'
       ,max(b.season) as 'Seasons', a.source, cast(a.Rating as float) as 'Rating'
       ,cast((cast(sum(CASE WHEN cast(c.Video_Height as int) >= 700 THEN 1 ELSE 0 END) as float)/count(*))*100 as int) as 'HD(%)' -- # 1080p/720p count as HD(%)
       ,sum(CASE WHEN cast(c.Video_Height as int) < 5000 AND cast(c.Video_Height as int) >= 1090 THEN 1 ELSE 0 END) as 'UHD'
       ,sum(CASE WHEN cast(c.Video_Height as int) < 1090 AND cast(c.Video_Height as int) >= 960 THEN 1 ELSE 0 END) as '1080p'
       ,sum(CASE WHEN cast(c.Video_Height as int) < 960 AND cast(c.Video_Height as int) >= 880 THEN 1 ELSE 0 END) as '900p/HD+'
       ,sum(CASE WHEN cast(c.Video_Height as int) < 880 AND cast(c.Video_Height as int) >= 700 THEN 1 ELSE 0 END) as '720p'
       ,sum(CASE WHEN cast(c.Video_Height as int) < 700 AND cast(c.Video_Height as int) >= 300 THEN 1 ELSE 0 END) as 'SD'
       ,sum(CASE WHEN cast(c.Video_Height as int) < 300 THEN 1 ELSE 0 END) as 'Low SD'
       ,sum(CASE WHEN c.tvepid is null THEN 1 ELSE 0 END) as 'Missing'
       ,cast((cast(sum(CASE WHEN c.tvepid is null THEN 1 ELSE 0 END) as float)/count(*))*100 as int) as 'Missing(%)'
       ,sum(CASE WHEN c.tvepid is not null THEN 1 ELSE 0 END) as 'In Library'
       ,cast((cast(sum(CASE WHEN c.tvepid is not null THEN 1 ELSE 0 END) as float)/count(*))*100 as int) as 'In Library(%)'
       ,count(*) as 'Total(In Library&Missing)'
       ,min(cast(c.Video_Width as int)) as 'MinWidth', max(cast(c.Video_Width as int)) as 'MaxWidth'
       ,min(cast(c.Video_Height as int)) as 'MinHeight', max(cast(c.Video_Height as int)) as 'MaxHeight'
       ,cast(avg(Video_Duration)/60 as integer) as 'AverageRuntime'
FROM TVShow a
     LEFT OUTER JOIN (select idShow, min(aired) as aired from episode where aired > date('now') group by idShow) x on x.idShow = a.idShow,
     episode b LEFT OUTER JOIN TVVStreams c on b.idEpisode = c.tvepid
WHERE a.idShow = b.idShow
  AND (c.streamid = 0 OR c.streamid is null) -- # Note: Only include the first video stream or missing episodes (null)  
  AND b.season > 0 -- # Note: comment this line to include Specials (Season 0)
  AND b.aired != '' AND b.aired <= date('now') -- # Note: comment this line to get all episodes, not just those that have been aired already
GROUP BY a.Title, a.Premiered, a.Rating
ORDER BY a.Title asc
4x R-Pi4b LibreELEC v9.2.5 | Bello 7 | Flirc cases
Storage Synology DS411 | 4 x WD RED 6TB
Software MariaDB 10.4.15 | Filebot | Ember Media Manager
wiki (wiki) | First time user (wiki) | Debug_Log (wiki) | mysql (wiki) | artwork (wiki)
Reply



Logout Mark Read Team Forum Stats Members Help
Ember TV Library analysis query0
This forum uses Lukasz Tkacz MyBB addons.