locked
Ways to compare distribution by bin? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi 

    I have a table, which has 4 columns with

    |Device_Model | Device_Id | Bin | DurationInBin|

    The Bin number is from 1 to 20. DurationBin is the time spent in each Bin number by each device. 

    We want to compare the distribution of DurationInBin by device_model. My initial attempt is to sum up the DurationInBin by Bin then compare the sum across models. The downside is that the value could be blown up if some devices stay in one bin for a long time and skew the result. 

    What're the other ways to see the distribution?

    Thanks for the help.


    Wednesday, June 17, 2020 5:50 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    hi

    Please provide some query and more clarity that you attempt

    Thanks

    Wednesday, June 17, 2020 7:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    For example, here's the data we see

    Device_Model,Device_Id,Bin,DurationInBin
    m2, 13027,1,0
    m2, 13027,2,20
    m2, 13027,3,30
    m2, 13027,4,34
    m2, 13027,5,25
    m2, 13027,6,0
    m2, 13027,7,0
    m2, 13027,8,0
    m2, 13027,9,0
    m2, 13027,10,0
    m2, 13027,11,0
    m2, 13027,12,0
    m2, 13027,13,0
    m2, 13027,14,0
    m2, 13027,15,0
    m2, 13027,16,0
    m2, 13027,17,0
    m2, 13027,18,0
    m2, 13027,20,0
    m3, 14924,1,0
    m3, 14924,2,0
    m3, 14924,3,0
    m3, 14924,4,24
    m3, 14924,5,28
    m3, 14924,6,37
    m3, 14924,7,30
    m3, 14924,8,10
    m3, 14924,9,5
    m3, 14924,10,0
    m3, 14924,11,0
    m3, 14924,12,0
    m3, 14924,13,0
    m3, 14924,14,0
    m3, 14924,15,0
    m3, 14924,16,0
    m3, 14924,17,0
    m3, 14924,18,0
    m3, 14924,20,55





    Wednesday, June 17, 2020 8:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    hi

    The bellow query may help you

    with machin(model,Id,bin,duration)as
(
select d.Device_Model,d.Device_Id,d.bin,sum(d.DurationInBin) from distribution_by1_bin  d
group by d.Device_Model,d.Device_Id,d.bin
),result(model,Id,bin,duration,ranks)as
(
select model,Id,bin,duration,rank() over(partition by model order by duration desc) ranks
from machin
)
select model,Id,bin,duration from result where ranks = 1
    here the table name i provide  
    distribution_by1_bin

    Thanks and Regards

    Laxmidhar sahoo


    Thursday, June 18, 2020 11:27 AM