c# - How create groups with evenly sizes -
i have table car information dates.
car_id date <aditional info> 1 20160101 2 20160102 1 20160103 2 20160104 1 20160105 1 20160106 1 20160107 1 20160108 1 20160109 1 20160110
if car has <= 5
records have 1 group. case car_id = 2
if car has <= 10
records have 2 evenly group. case car_id = 1
order date
- 20160101 - 20160106
group 1
- 20160107 - 20160110
group 2
if car has '> 10' have 3 evenly distributed groups.
desire output.
car_id date group_id 1 20160101 1 1 20160103 1 1 20160105 1 1 20160106 1 ------------------------ 1 20160107 2 1 20160108 2 1 20160109 2 1 20160110 2 ------------------------ 2 20160102 1 2 20160104 1
i try ntile()
cant make group number dinamic.
select car_id, "date", ntile(3) on (partition car_id order "date") group_id table1
bonus points if can done direct on
c# linq
, otherwise create function on postgres.
aditional info display historic car information on different colors (groups) cars small amount of data appear single color. , max number of colors 3.
you use ntile
:
select car_id, "date", ntile(case when c <= 5 1 when c <= 10 2 else 3 end) on (partition car_id order "date") group_id (select car_id, "date",count(*) over(partition car_id) c table1) s
output:
╔════════╦══════════╦══════════╗ ║ car_id ║ date ║ group_id ║ ╠════════╬══════════╬══════════╣ ║ 1 ║ 20160101 ║ 1 ║ ║ 1 ║ 20160103 ║ 1 ║ ║ 1 ║ 20160105 ║ 1 ║ ║ 1 ║ 20160106 ║ 1 ║ ║ 1 ║ 20160107 ║ 2 ║ ║ 1 ║ 20160108 ║ 2 ║ ║ 1 ║ 20160109 ║ 2 ║ ║ 1 ║ 20160110 ║ 2 ║ ║ 2 ║ 20160102 ║ 1 ║ ║ 2 ║ 20160104 ║ 1 ║ ╚════════╩══════════╩══════════╝
Comments
Post a Comment