CODEWITHSUNDEEP
postgresql
analyst92
analyst92

Reputation: 257

Count the number of instances the time is above average time

Here is my code:

arrival_cluster_raw as (

SELECT 
       routes.uc_id ,
       cg.cluster_id ,
       cg.cluster_centroid ,
       routes.imei ,
       routes.time_created::date as campaign_date,
       min(routes.time_created) as m_per_imei_cluster 
FROM cluster_groups as cg
group by 1,2,3,4,5

)
,
arrival_cluster_final as 
(
select uc_id, campaign_date, cluster_id, cluster_centroid , date_trunc('second', AVG(m_per_imei_cluster::TIME)) as avg_arrival_time,
count(case when m_per_imei_cluster::TIME < (select AVG(m_per_imei_cluster::TIME) from arrival_cluster_raw) then 1 else null END) as "num_of_arrival_teams_before_avg_time"
,count(case when m_per_imei_cluster::TIME > (select AVG(m_per_imei_cluster::TIME) from arrival_cluster_raw) then 1 else null END) as "num_of_arrival_teams_after_avg_time"

FROM arrival_cluster_raw
group by uc_id,cluster_id, cluster_centroid ,campaign_date
)

The problem is that in the "arrival_cluster_final", the average value of the entire cluster is being compared whereas I want to compare the average value for the combination of uc_id,cluster_id, cluster_centroid ,campaign_date

Upvotes: 0

Views: 539

Answers (1)

jian
jian

Reputation: 4824

--can you try this one.

WITH arrival_cluster_raw AS (
        SELECT
            routes.uc_id,
            cg.cluster_id,
            cg.cluster_centroid,
            routes.imei,
            routes.time_created::date AS campaign_date,
            min(routes.time_created) AS m_per_imei_cluster
        FROM
            cluster_groups AS cg
            JOIN routes ON routes.uc_id = cg.id --assume the way you want join.
        GROUP BY
            1,2,3,4,5
    ),
    arrival_cluster_final AS (
        SELECT
            uc_id,
            cluster_id,
            cluster_centroid,
            imei,
            campaign_date,
            date_trunc('second', (avg(m_per_imei_cluster) OVER w)) 
            ,count( CASE WHEN (avg(m_per_imei_cluster) OVER w) < m_per_imei_cluster THEN
                1
            ELSE
                NULL
            END) AS num_of_arrival_teams_before_avg_time
            ,count(
                CASE WHEN (avg(m_per_imei_cluster) OVER w) > m_per_imei_cluster THEN
                    1
                ELSE
                    NULL
                END) AS num_of_arrival_teams_after_avg_time
        FROM
            arrival_cluster_raw
    WINDOW w AS (PARTITION BY uc_id,
        cluster_id,
        cluster_centroid,
        campaign_date))
    SELECT * FROM arrival_cluster_final ORDER BY 1;

Upvotes: 1

Related Questions