CODEWITHSUNDEEP
sqlmariadbquery-optimizationrow-number
MMitrich
MMitrich

Reputation: 11

MariaDb, calculating median value for limited number of values from linked table

Having two tables, representing phones and their out-coming calls, I'd like to get list of phone numbers along with median value of last 5 call duration, in case there is at least 5 calls for number listed.

The following query works

select distinct sq.phone_number, 
        median(duration) OVER (PARTITION by sq.phone_number)  from (

    SELECT 
     p.phone_number, c.duration , 
     ROW_NUMBER() OVER(PARTITION BY p.phone_number order by c.startDateTime desc) as rn,
     count(*) OVER(PARTITION BY p.phone_number) cnt
    from phones p 
    join calls c on c.phone_id = p.id 
) sq
where rn<=5 and cnt>=5;

but there are performance concerns, because inner query returns all the calls, not latest ones. Is it possible to limit rows returned by inner query, or to rewrite the whole query in another way?

Upvotes: 1

Views: 46

Answers (0)

Related Questions