As always, great question from Sunil. My approach without using the inbuilt percentile_cont function:- Declare @total int; Set @total = (Select sum(num_users) from search_frequency) ; with cte_rec_median as ( Select searches, num_users, 1 as step from search_frequency union all select searches, num_users, 1+step from cte_rec_median where step < num_users ) Select CASE WHEN @total%2 = 0 THEN CAST(sum(CAST(searches as Decimal(38,1))/2) as Decimal(38,1)) ELSE sum(searches) END as median from ( Select searches, CASE WHEN @total%2 = 0 THEN CASE WHEN rn = @total/2 or rn = @total/2+1 THEN 1 ELSE 0 END ELSE CASE WHEN rn = @total/2+1 THEN 1 ELSE 0 END END as Indicator from ( Select searches, num_users, step, row_number() over(order by searches, num_users) as rn from cte_rec_median )sub )sub2 where Indicator = 1
@renganathanmutthiah9150
Жыл бұрын
Awesome Sunil!! Below is another solution... with temp1 as /* holds exact median value of the sum is odd; else holds two values whose avg is median */ (select * from ( select case when sum(num_users)%2 =0 then sum(num_users)/2 end as col from search_frequency union select case when sum(num_users)%2 =0 then (sum(num_users)/2)+1 end as col from search_frequency union select case when sum(num_users)%2 =1 then sum(num_users)/2 end as col from search_frequency ) as a where col is not null ) ,temp2 as /* calcs running total of num_users and a lag of prev running total */ (select searches ,num_users ,cumulative_sum ,COALESCE(lag(cumulative_sum,1) over(),-1) as prev_cumulative_sum from ( select searches ,num_users ,sum(num_users) over(order by searches rows between unbounded preceding and current row) as cumulative_sum from search_frequency order by searches ) as a ) select round(avg(searches),1) as median from ( select searches ,case when ( (select min(col) from temp1) between prev_cumulative_sum+1 and cumulative_sum OR (select max(col) from temp1) between prev_cumulative_sum+1 and cumulative_sum ) then 'Y' else 'N' end as choose from temp2 ) A where choose = 'Y'
@ItJunction4all
Жыл бұрын
Thanks Renga for your query. I just went through your query and executed. It threw error. Looks like you have used Order by clause in subquery which is not allowed. Another issue is that you have kept OVER clause empty in lag function where ORDER BY clause is mandatory. Let me know if I missed to understand your code.
@arunkumarn8710
Жыл бұрын
with cte as (select searches, num_users, 1 as temp from search_frequency union all select searches, num_users, temp +1 from cte where temp+1
@akashgoel601
Жыл бұрын
thanks for this, can you consider posting videos in 720p or more.. this resolution is too old now.
@ItJunction4all
Жыл бұрын
Hi Akash, I have made this video in 4k pixel. Probably you will need to change the settings . You will have to go to settings and then Advance and then choose the pixel. Currently I can see 2160 p is available to watch.
@akashgoel601
Жыл бұрын
@@ItJunction4all thanks for that, I am not sure what happened before while I was trying to access, it was just showing 360p as an option. Looks good now.
@ItJunction4all
Жыл бұрын
Ok . Np Akash :)
@akashgoel601
Жыл бұрын
@@ItJunction4all anyways missed your new video! 😊 Thanks for posting one now
@reachrishav
Жыл бұрын
Another approach. SELECT AVG(1.*searches) median FROM ( SELECT searches, row_number() over(order by searches) rn, count(*) over() cnt FROM search_frequency f CROSS APPLY GENERATE_SERIES(1, f.num_users) ) x WHERE rn BETWEEN 1.*cnt/2 AND 1.*cnt/2 + 1
Пікірлер: 10