可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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

If car has <= 5 records then will have only one group. CASE car_id = 2

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

if car has '> 10' then will have three 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 with ntile() but cant make the group number dinamic.

SQL Fiddle Demo

SELECT car_id, 
       "date",
       ntile(3) over (partition by car_id order by "date") as group_id
FROM Table1

Bonus Points if can be done direct on C# LINQ, otherwise I create a function on postgres.

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.

回答1:

You could use 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

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 ║
╚════════╩══════════╩══════════╝

回答2:

I would calculate the groups manually, using row_number() and count():

select t1.*,
       (case when cnt <= 5 then 1
             when car_id * 2 <= cnt then 1
             else 2
        end) as grp
from (select t1.*,
             row_number() over (partition by car_id order by date) as seqnum,
             count(*) over (partition by car_id) as cnt
     from table1
    ) t
where cnt <= 10
order by car_id, grp, date;

回答3:

SQL Fiddle Demo

As Gordon suggest.

First calculate the number of dates for each car_id
Then assign the number of groups I want depending on cnt
Then use groups for the ntile(grp) analytic function

WITH car_dates_count as (
  select t1.*,             
         count(*) over (partition by car_id) as cnt
  from table1 t1
),
car_groups as (
  select cdc.*,
       (case when cnt <= 5  then 1
             when cnt <= 10 then 2
                            else 3
        end) as grp
  from car_dates_count cdc
) 
SELECT *,
       ntile(grp) over (partition by car_id order by "date") as group_id
FROM car_groups;

OUTPUT

| car_id |     date | cnt | grp | group_id |
|--------|----------|-----|-----|----------|
|      1 | 20160101 |   9 |   2 |        1 | \
|      1 | 20160102 |   9 |   2 |        1 |  |
|      1 | 20160103 |   9 |   2 |        1 |  |
|      1 | 20160104 |   9 |   2 |        1 |  |
|      1 | 20160105 |   9 |   2 |        1 |  |=> (cnt 9 <= 10) Mean two groups
|      1 | 20160106 |   9 |   2 |        2 |  |
|      1 | 20160107 |   9 |   2 |        2 |  |
|      1 | 20160108 |   9 |   2 |        2 |  |
|      1 | 20160109 |   9 |   2 |        2 | /
--------------------------------------------
|      2 | 20160101 |   5 |   1 |        1 | \
|      2 | 20160102 |   5 |   1 |        1 |  |
|      2 | 20160103 |   5 |   1 |        1 |  |=> (cnt 5 <= 5) Mean one group
|      2 | 20160104 |   5 |   1 |        1 |  |
|      2 | 20160105 |   5 |   1 |        1 | /
--------------------------------------------
|      3 | 20160101 |  16 |   3 |        1 | \
|      3 | 20160102 |  16 |   3 |        1 |  |
|      3 | 20160103 |  16 |   3 |        1 |  |  
|      3 | 20160104 |  16 |   3 |        1 |  |
|      3 | 20160105 |  16 |   3 |        1 |  |
|      3 | 20160106 |  16 |   3 |        1 |  |
|      3 | 20160107 |  16 |   3 |        2 |  |
|      3 | 20160108 |  16 |   3 |        2 |  |=> (cnt 16 > 10) Mean three groups
|      3 | 20160109 |  16 |   3 |        2 |  |
|      3 | 20160110 |  16 |   3 |        2 |  |
|      3 | 20160111 |  16 |   3 |        2 |  |
|      3 | 20160112 |  16 |   3 |        3 |  |
|      3 | 20160113 |  16 |   3 |        3 |  |
|      3 | 20160114 |  16 |   3 |        3 |  |
|      3 | 20160115 |  16 |   3 |        3 |  |
|      3 | 20160116 |  16 |   3 |        3 | /

回答4:

Assuming you have Car class defined as follows, you could do this using Linq.

public class Car
{
    public int car_id;
    public DateTime date;       
    // additional info
}

We need to apply grouping twice, once on car_id and next on when evenly splitting to multiple groups. I prefer using Linq for this.

    var grouped = cars.GroupBy(c=>c.car_id)
        .Select(c => 
                new 
                {
                    car_grp_id = c.Key,
                    splits = c.Select((s,i)=> 
                    new 
                    {
                        grp_id = i/(c.Count() <= 5 ? 5 :(c.Count() %2 ==0)? c.Count() /2 : (c.Count() /3 +1)),
                        item = s
                    }), 

                })          
        .Select(s=> 
                new 
                {                       
                    grouponcars = s.splits.GroupBy(g=>g.grp_id)
                        .Select(x=>
                        new 
                        {
                            group_id = x.Key,
                            cars = x.Select(y=>y.item)
                        })
                })
        .ToList();

Output

groupid : 0 -       Car_Id : 1 -         Date -1/1/2016 12:00:00 AM
groupid : 0 -       Car_Id : 1 -         Date -1/3/2016 12:00:00 AM
groupid : 0 -       Car_Id : 1 -         Date -1/5/2016 12:00:00 AM
groupid : 0 -       Car_Id : 1 -         Date -1/6/2016 12:00:00 AM
----------------------------------------
groupid : 1 -       Car_Id : 1 -         Date -1/7/2016 12:00:00 AM
groupid : 1 -       Car_Id : 1 -         Date -1/8/2016 12:00:00 AM
groupid : 1 -       Car_Id : 1 -         Date -1/9/2016 12:00:00 AM
groupid : 1 -       Car_Id : 1 -         Date -1/10/2016 12:00:00 AM
----------------------------------------
groupid : 0 -       Car_Id : 2 -         Date -1/2/2016 12:00:00 AM
groupid : 0 -       Car_Id : 2 -         Date -1/4/2016 12:00:00 AM

Check this Demo