如何创建大小均匀的组 [英] How create groups with evenly sizes
问题描述
我有一张桌子,上面有按日期列出的汽车信息.
I have a table with car information by 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
如果汽车有<= 5
条记录,则只有一组. CASE car_id = 2
If car has <= 5
records then will have only one group. CASE car_id = 2
如果汽车具有<= 10
记录,则将有两个平均组. CASE car_id = 1
按日期排序
if car has <= 10
records then will have two evenly group. CASE car_id = 1
order by date
- 20160101-20160106
GROUP 1
- 20160107-20160110
GROUP 2
- 20160101 - 20160106
GROUP 1
- 20160107 - 20160110
GROUP 2
如果汽车的'> 10'则将具有三个均匀分布的组.
if car has '> 10' then will have three evenly distributed groups.
目标输出.
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
我尝试使用ntile()
,但是无法使组号清晰可见.
I try with ntile()
but cant make the group number dinamic.
SELECT car_id,
"date",
ntile(3) over (partition by car_id order by "date") as group_id
FROM Table1
如果可以直接在
C# LINQ
上完成奖励积分,否则我将在postgres上创建一个函数.
Bonus Points if can be done direct on
C# LINQ
, otherwise I create a function on postgres.
附加信息,我将以不同的颜色(组)显示历史汽车信息,因此,数据量较小的汽车将以单一颜色显示.最多可以有3种颜色.
aditional info I will display the historic car information on different colors (groups) So cars with small amount of data will appear with a single color. And max number of colors will be 3.
推荐答案
您可以使用ntile
:
SELECT car_id, "date",
ntile(CASE WHEN c <= 5 THEN 1
WHEN c <= 10 THEN 2
ELSE 3
END) OVER (PARTITION BY car_id ORDER BY "date") AS group_id
FROM (SELECT car_id, "date",COUNT(*) OVER(PARTITION BY car_id) AS c
FROM Table1) AS s
输出:
╔════════╦══════════╦══════════╗
║ 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 ║
╚════════╩══════════╩══════════╝
这篇关于如何创建大小均匀的组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!