Calculation of median value for every day

Question

Can anybody help me to calculate median value for each date?

activity
user_id login_time  bet
105 2018-04-01  20966119
102 2018-04-01  2027700
105 2018-04-01  5478000
104 2018-04-01  78448383
104 2018-04-06  49730093
101 2018-04-06  2750000
103 2018-04-15  16625000
105 2018-04-16  
106 2018-04-19  3095584

Output

login_time   median
2018-04-01   13222060
2018-04-06    26240047

My query

SELECT login_time, IF(SUM((IFNULL(bet, 0)) / 2) % 2 = 0, SUM(IFNULL(bet, 0)) 
    / 2 + 1, SUM(IFNULL(bet, 0)) / 2) 
 AS median
FROM activity
WHERE login_time BETWEEN '2018-04-01' AND '2018-04-06'
GROUP BY login_time;

Whay is wrong? Thanks!

Are you sure you want the median? Can you explain your current attempted logic? — Gordon Linoff, Sep 03 '18 at 13:10
@Gordon Linoff, yes, I need median value. May be my query is wrong. But need for help — Елисей Горьков, Sep 03 '18 at 13:14
@Raymond Nijland, this result I've got in Excel. What should I change to get rigtht result? — Елисей Горьков, Sep 03 '18 at 13:15
Did you mean the average? because 26240047 ==> (49730093 + 2750000) / 2 — Vanojx1, Sep 03 '18 at 13:15
"What should I change to get rigtht result? " Start by updating the question.. That the output is a result of the query that's below and not a expected result set.. Makes your question beter to read and understand for other further readers. — Raymond Nijland, Sep 03 '18 at 13:18
@Vanojx1 No. Excel shoes shuch result with function =МЕДИАНА(J9:J10) (russian edition) — Елисей Горьков, Sep 03 '18 at 13:19
If you use MySQL https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql — Raymond Nijland, Sep 03 '18 at 13:21
@Raymond Nijland I use sql of cource, but check result in Excel sometimes — Елисей Горьков, Sep 03 '18 at 13:21
"Did you mean the average? because 26240047 ==> (49730093 + 2750000) / 2" topicstarter and @Vanojx1.. Vanojx1 is more or less right Median wil use the AVG of two orderd middle rows when the items count is even..Median wil pick the middle item from a order list when the item count is odd. — Raymond Nijland, Sep 03 '18 at 13:29

Gordon Linoff · Answer 1 · 2018-09-03T19:20:49.853

0

There is a group_concat() trick for this, if your data is not too large. This works pretty well:

select a.login_time,
       substring_index(substring_index(group_concat(bet order by bet), ',', ceil(count(*) / 2), ',', -1)
from activity a
where a.login_time between '2018-04-05' and '2018-04-18'
group by a.login_time;

If there are an even number of examples, then this chooses the value on the lower side. Medians are not well defined for sets with an even cardinality.

edited Sep 03 '18 at 19:20

answered Sep 03 '18 at 14:23

Gordon Linoff

1,242,037
58
646
786

thanks, but: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'su bstring_index' – Елисей Горьков Sep 03 '18 at 14:28
this does not handle median complety..Correct median calculation wil use the AVG of two orderd middle rows when the items count is even..median wil pick the middle item from a order list when the item count is odd. This query does only handle the even count – Raymond Nijland Sep 03 '18 at 16:08

Calculation of median value for every day

1 Answers1