complicated SQL:how to using a query to compute rank of order(Outstanding of Class 1)

[bbaaking]

Hi,

I have be in trouble for 2 weeks, hope some nice people can help me.

Background:There are some technical service data (ServiceID, TCID, EndTime, and QoS) submitted by client in a whole year about technical support, and there are a unique primary key (ID by name) because there are some duplicates. I must use one single query statement(not insert/delete/update, and not stored procedure) to process a computation, because the client did not allow me to write in the database. And this computation is some complicated (so It is divided up 4 aims, and It should be a single SQL at last).

The whole goal can be divided up some aims(but I need just final goal), and I have started a thread about first one, and I have got correct answer. And There are 3 aims left, so I start this thread to find some help to reach final goal. thank you.

Data Structure:

    ID: Unique primary key of record
    ServiceID: ID of a certain service
    TCID: ID of a technical support engineer
    EndTime: Ending Time of a service
    Qos：Quality of service (1 Satisfied; 2 Average; 3 Unsatisfactory/Dissatisfied). 

DDL and insert SQL(mysql5):

    CREATE TABLE `service` (
      `ServiceID` INTEGER(11) NOT NULL,
      `ID` INTEGER(11) NOT NULL ,
      `TCID` VARCHAR(40)  NOT NULL,
      `EndTime` DATE NOT NULL,
      `Qos` CHAR(1)  NOT NULL,
      PRIMARY KEY (`ID`),
      UNIQUE KEY `ID` (`ID`)
    );
    COMMIT;
    INSERT INTO `service` (`ServiceID`, `ID`, `TCID`, `EndTime`, `Qos`) VALUES
      (2004, 9, 'Jacob', '2011-02-04', '1'),
      (2000, 2, 'Jacob', '2011-01-01', '2'),
      (2000, 3, 'Jacob', '2011-01-01', '2'),
      (2001, 4, 'Jacob', '2011-01-01', '2'),
      (2002, 5, 'Jacob', '2011-02-03', '1'),
      (2003, 6, 'Tyler', '2011-01-04', '1'),
      (2003, 7, 'Tyler', '2011-01-04', '1'),
      (2003, 8, 'Tyler', '2011-01-03', '2'),
      (2005, 10, 'Jacob', '2011-02-05', '1'),
      (2006, 11, 'Jacob', '2011-02-04', '2'),
      (2007, 12, 'Jacob', '2011-01-08', '1'),
      (2008, 13, 'Tyler', '2011-02-06', '1'),
      (2009, 14, 'Dylan', '2011-02-08', '1'),
      (2010, 15, 'Dylan', '2011-02-09', '1'),
      (2014, 16, 'Andrew', '2011-01-01', '1'),
      (2013, 17, 'Andrew', '2011-01-01', '1'),
      (2012, 18, 'Andrew', '2011-02-19', '1'),
      (2011, 19, 'Andrew', '2011-02-02', '1'),
      (2015, 20, 'Andrew', '2011-02-01', '1'),
      (2016, 21, 'Andrew', '2011-01-19', '1'),
      (2017, 22, 'Jacob', '2011-01-01', '1'),
      (2018, 23, 'Dylan', '2011-02-03', '1'),
      (2019, 24, 'Dylan', '2011-01-09', '1'),
      (2020, 25, 'Dylan', '2011-01-01', '1'),
      (2021, 26, 'Andrew', '2011-01-03', '1'),
      (2021, 27, 'Dylan', '2011-01-11', '1'),
      (2022, 28, 'Jacob', '2011-01-09', '1'),
      (2023, 29, 'Tyler', '2011-01-19', '1'),
      (2024, 30, 'Andrew', '2011-02-01', '1'),
      (2025, 31, 'Dylan', '2011-02-03', '1'),
      (2026, 32, 'Jacob', '2011-02-04', '1'),
      (2027, 33, 'Tyler', '2011-02-09', '1'),
      (2028, 34, 'Daniel', '2011-01-06', '1'),
      (2029, 35, 'Daniel', '2011-02-01', '1');
     COMMIT;

some of data

ServiceID    ID    TCID    EndTime        Qos
2000    2    Jacob    2011-01-01    2
2000    3    Jacob    2011-01-01    2
2001    4    Jacob    2011-01-01    2
2002    5    Jacob    2011-02-03    1
2003    6    Tyler        2011-01-04    1
2003    7    Tyler        2011-01-04    1
2003    8    Tyler        2011-01-03    2
2004    9    Jacob    2011-02-04    1

ami1:fliter the duplicate data with query statment( select not delete),that is, the various entries of EndTime and Qos for a same ServiceID due to the repeated feedback. The rule is to only keep the records with the lowest Qos for a same ServiceID. If there are several records having the lowest Qos, then only keep whatever one of these records. The client accepts and prefers to this scheme. In this example, for the ID=6,7, and 8, just keep 6 or 7. this aim had been solved (plz go on with 2-4)as below:

SELECT DISTINCT serviceid,tcid,endtime,qos FROM (SELECT * FROM service ORDER BY serviceid, qos, id) AS base GROUP BY serviceid

aim2:Qos=1 represents the Satisfied Service. The monthly total Satisfied Services achieved by each technical support engineer is referred to as “Personal Total Satisfied Services this Month”. Those technical support engineers whose “Personal Total Satisfied Service this Month” ranking among the top 2 in that month are referred to as the “Top 2 since this Month”. If they are outstanding enough to be entitled to the “Top 2 since this Month” every month, then they can be referred to as “Outstanding of Class 1”. All in all, this step is to compute the “Outstanding of Class 1”. In this example, “Top 2 since this Month” for January is Andrew and Jacob, and that for February are Andrew, Dlyan, and Jacob. Therefore, the honor title of “Outstanding of Class 1” is awarded to Andrew and Jacob(just these 2 recorders).

aim3:Then, to compute the “Outstanding of Class 2” (the engineers of “Top 2 since this Month” are not included) equals to compute those ranking the third and the forth places. In this example, the “Outstanding of Class 2” is the Tyler.

final goal:The final goal is to combine the “Outstanding of Class 1” with “Outstanding of Class 2”. The result will be ultimately transferred to report for rendering. My dataSet is just like:

TCID           level
Andrew       top2
Jacob         top2
Tyler           top4

plz help

[Luuk]

aim2:

set @a:=0; 
select P, tcid, RANK 
from (
    select P, tcid, @a:=CASE WHEN @p=P THEN @a+1 ELSE 1 END  RANK, @p:=P 
    from (select concat(year(EndTime), lpad(format(month(EndTime),0),2,'0')) P, tcid, count(*) C  
    from (
        SELECT DISTINCT serviceid,tcid,endtime,qos 
        FROM (
            SELECT * 
            FROM service 
            ORDER BY serviceid, qos, id) AS base 
        GROUP BY serviceid) X 
    group by EndTime, tcid 
    order by year(EndTime), month(EndTime), C desc )X2 
)X3 where RANK<=2;

outputs:

+--------+--------+------+
| P      | tcid   | RANK |
+--------+--------+------+
| 201101 | Jacob  |    1 |
| 201101 | Andrew |    2 |
| 201102 | Jacob  |    1 |
| 201102 | Andrew |    2 |
+--------+--------+------+

aim3: This will be more of the same concept, i think.... ;)

[bbaaking]

Thank you, luuk.
I noticed that your statement is almost right,but there must be some mistaken, because I want rank but not rownumber by order.
as an example: 1,1,10 rank is 1,1,3. otherwise, I cann't chose the people with the same data.

after filtering duplicate date, the TCID and cnt(Personal Total Satisfied Services this Month) and rank should be:

month    cnt    TCID    rank
1    4    Andrew    1
1    1    Daniel    5
1    2    Dyla    3
1    3    Jacob    2
1    2    Tyler    3
2    4    Andrew    1
2    1    Daniel    5
2    4    Dyla    1
2    4    Jacob    1
2    2    Tyler    4

according to data above:
Top 2 since January is: Andrew, Jacob
Top 2 since February is: Andrew, Dyla, Jacob
so Outstanding of Class 1 is: Andrew, Jacob

Top 4 since January is: Dyla, Tyler
Top 4 since February is: Tyler
so Outstanding of Class 2 is: Tyler

please continue to help me.
BTW, I find a way without a SQL to solve this problem. But I want a SQL solution indeed.

[Luuk]

OK, that will be too hard for /me now...
Especially if you want to do it in MySQL only.
Writing a short script in i.e. PHP would make things easier.

[bbaaking]

hi luuk,
thanks for your work though you didn't solve this problem. You are a nice guy.
I must use SQL because I should return a dataset/resultSet to reporting tools , Jasper only identify SQL ( JAVA bean may does too, but It seems too complicated).
I find a solution(http://stackoverflow.com/questions/1...75434#10275434).
that is using esProc to solve this problem. esProc is the only solution I found. Yes, It can return a resultSet object and invoked by report Tool ( I'm using Jasper). But I still need a SQL solution. so, please told me If there are any SQL solution. Thanks for your reply.

Here are some information about this solution: http://www.esproc.com/tour/what-is-esproc.html

Or here is a SQL Server Solution(but not fit for mysql):http://social.msdn.microsoft.com/For...2-e998ea3387d9

Hope this helps people that meet the same trouble.

[Rabbit]

After removing your duplicates, and returning your counts by TCID and Month, you can join the result to itself to do a ranking query. With that, you can return your top 2 for each month, do a count by TCID, and return those where the count is equal to how many months of data you have.

[bbaaking]

hi Rabbit,
thanks for your reply.
but your suggestion is out of my ability, I can't do it correctly. as you know, there seems no "with","over","partition by" in mysql syntax. I can't write it out. so I need a help here.

[Rabbit]

You don't need the row number partition by syntax. I didn't say you had to use that.

This syntax is SQL Server, you will have to make minor adjustments to get it to work in MySQL. For example, the CONVERT function is slightly different in MySQL. Be sure to note that this only works for a single year, if it needs to span multiple years, you will have to bring in a year into the query.

SELECT TCID, Class
FROM (
    SELECT T1.TCID, T1.EndMonth, 
        FLOOR(CONVERT(FLOAT, (CASE WHEN T2.EndMonth IS NULL THEN 0 ELSE COUNT(*) END)) / 2.0) + 1 AS Class
    FROM (
        SELECT T.TCID, MONTH(EndTime) AS EndMonth, COUNT(*) AS NumSatisfied
        FROM Service T
            INNER JOIN (
                SELECT ServiceID, MIN(ID) AS ID
                FROM Service
                WHERE Qos = 1
                GROUP BY ServiceID
            ) MinID
            ON T.ServiceID = MinID.ServiceID
                AND T.ID = MinID.ID
        GROUP BY T.TCID, MONTH(EndTime)
    ) T1 LEFT JOIN (
        SELECT T.TCID, MONTH(EndTime) AS EndMonth, COUNT(*) AS NumSatisfied
        FROM Service T
            INNER JOIN (
                SELECT ServiceID, MIN(ID) AS ID
                FROM Service
                WHERE Qos = 1
                GROUP BY ServiceID
            ) MinID
            ON T.ServiceID = MinID.ServiceID
                AND T.ID = MinID.ID
        GROUP BY T.TCID, MONTH(EndTime)
    ) T2
    ON T1.EndMonth = T2.EndMonth
        AND T1.NumSatisfied < T2.NumSatisfied
    GROUP BY T1.TCID, T1.EndMonth, T2.EndMonth
    HAVING (CASE WHEN T2.EndMonth IS NULL THEN 0 ELSE COUNT(*) END) < 4
) Final
GROUP BY TCID, Class
HAVING COUNT(*) = 2
ORDER BY Class;

[Luuk]

@bbaaking
hmm, is your 'fina' goal a MySQL, of a MSSQL solution?

[Rabbit]

They want a MySQL solution but I don't have that at work. So that's a MSSQL solution. It should be almost directly portable to MySQL with the exception of the CONVERT call. That would have to be modified slightly. But the rest is standard SQL and should run fine in MySQL.

[bbaaking]

hi Luuk,
I need a MySQL solution.
I have got a MSSQL solution from a friend, but I(and this friend) can't onvert it to MySQL, for they are too different, and It is out of my ability.
check the MSSQL solution:

WITH MonthCount AS (SELECT   DATEDIFF(mm, MIN(EndTime), MAX(EndTime)) + 1 AS MonthCountField
                                    FROM      service),
rank_of_person as(
        select 
            [TCID], 
            TheMonth,
            [Personal_Total_Satisfied_Services_this_Month],
            rank() over 
                (partition by TheMonth order by [Personal_Total_Satisfied_Services_this_Month] desc) R
        from (
            select 
                [TCID], 
                month(EndTime) TheMonth,
                count(*) as [Personal_Total_Satisfied_Services_this_Month]
            from (
                select *, row_number() over (partition by [ServiceID] order by ID)  RN
                from [Service]
            ) T
            where RN = 1 and [Qos] = 1
            group by [TCID],month(EndTime)
            --order by month(EndTime)
        ) T
)
select [TCID],'top2'
from (
    select 
        [TCID], 
        TheMonth,
        [Personal_Total_Satisfied_Services_this_Month],
        R
    from rank_of_person
    where R < 3
) T
group by [TCID]
having count(*) = (select MonthCountField from MonthCount)
union
select [TCID],'top4'
from (
    select 
        [TCID], 
        TheMonth,
        [Personal_Total_Satisfied_Services_this_Month],
        R
    from rank_of_person
    where R > =3 and R<=4
) T
group by [TCID]
having count(*) = (select MonthCountField from MonthCount)

[Rabbit]

Just use the solution I posted. All you need to do to port it is fix the convert function call to MySQL's version. And you may have to fiddle with the HAVING clause a little bit. The rest will work fine, you just need to make two changes.

[bbaaking]

hi Rabbit
As much as I know, mysql does not support these keywords:with,over,row_number,partition by.
I think It must be a hard job to make this conversion, and out of my ablility, Maybe out of the most people's ability.
Anyway, thanks for your suggestion.

Actually, I'm studying ESPROC as It is my only practical solution. I will take It as my final solution in the project at this friday if I can't make that SQL conversion out.
BTW, I found It is more easy than MYSQL syntax even MSSQL, and It is in my ability.

[Rabbit]

Again, I did not say you have to use those. The row_number function isn't even in my solution. I said use my solution. It doesn't contain row_number, and it will work in MySQL. You just need to fix the convert function and possibly the group by.

I don't know why you keep bringing up row_number when I never ever mention it in any of my posts. You're the one that keeps bringing it up and saying it doesn't work. Yes, I know it doesn't work. That's why my solution doesn't use them. So that it will work in MySQL.

[bbaaking]

sorry, I had made a big mistaken for I missed your code. I must be a fool. please forgive me.

I have changed the convert function, and met a error message: "Unknown column 'T2.EndMonth' in 'having clause'", It is just like what u have said.

I　will study your statement carefully, for It is too complicated to me to understand, then I will make more fix.

thank you.

[bbaaking]

hi Rabbit,
I googled "having" usage, and I tried this statement:
select TCID,ID v1, endTime from service group by TCID having v1>20
It is right. So there must be something wrong with "case when".
But how to fix it. I have no idea. Can you give me a further hint?

[Rabbit]

In the HAVING clause, you just need to get rid of the CASE statement and use COUNT(*) < 4