如何创建大小均匀的组 [英] 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.

SQL小提琴演示

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

SqlFiddleDemo

输出:

╔════════╦══════════╦══════════╗
║ 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屋！