We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Aggregation
  4. Weather Observation Station 20
  5. Discussions

Weather Observation Station 20

Sort by

recency

|

3750 Discussions

|

  • baokhanh546123
     + 0 comments

    WITH OrderValue AS ( SELECT LAT_N, ROW_NUMBER() OVER (ORDER BY LAT_N) AS row_num, COUNT(*) OVER () AS total_count FROM STATION ) SELECT ROUND(AVG(LAT_N),4) AS median FROM OrderValue WHERE row_num IN ((total_count + 1) / 2, (total_count + 2) / 2);

  • dylantayag25
     + 0 comments
    with ranked_latitude as (
    select 
         lat_n
        ,row_number() over (order by lat_n) as lat_ranking
        ,(select count(*) from station) as no_of_rows
        from station group by lat_n
),
    median as (
        select lat_ranking from ranked_latitude
        where lat_ranking = (
            case
                when (no_of_rows)%2=0 then (no_of_rows+1)/2
                else (no_of_rows+1)/2
            end
        )
    )
    select round(lat_n,4) from ranked_latitude where 
    lat_ranking = (select * from median);

  • subhasiruthas
     + 0 comments

    In MySQL, with rn as (select row_number() over (order by lat_n) as rownumber, lat_n, count(*) over() as c from station) select round(avg(lat_n),4) from rn where rownumber in ((c/2) + 1, (c+1)/2) ;

  • pvj_valadares
     + 0 comments
    WITH cte AS (
    SELECT LAT_N,
           ROW_NUMBER() OVER (ORDER BY LAT_N) AS ordena,
           COUNT(*) OVER () AS total_count
    FROM STATION
)

SELECT 
    ROUND(AVG(LAT_N), 4) AS median
FROM 
    cte
WHERE 
    ordena IN (FLOOR((total_count + 1) / 2), FLOOR((total_count + 2) / 2));

  • lgroverco
     + 1 comment

    SET @ec = (SELECT COUNT(lat_n) FROM station);

    WITH med AS (Select * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Lat_n) as rn, Lat_n, LEAD(lat_n,1) OVER (ORDER BY lat_n) as x FROM Station ) AS temp WHERE rn=(CEILING(@ec/2)) ) SELECT case WHEN @ec%2=1 THEN ROUND(lat_n,4) WHEN @ec%2=0 THEN ROUND((lat_n+x)/2,4) END FROM med

    ;