By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

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

P: 19
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:
Expand|Select|Wrap|Line Numbers
  1.     ID: Unique primary key of record
  2.     ServiceID: ID of a certain service
  3.     TCID: ID of a technical support engineer
  4.     EndTime: Ending Time of a service
  5.     Qos:Quality of service (1 Satisfied; 2 Average; 3 Unsatisfactory/Dissatisfied). 
DDL and insert SQL(mysql5):
Expand|Select|Wrap|Line Numbers
  1.     CREATE TABLE `service` (
  2.       `ServiceID` INTEGER(11) NOT NULL,
  3.       `ID` INTEGER(11) NOT NULL ,
  4.       `TCID` VARCHAR(40)  NOT NULL,
  5.       `EndTime` DATE NOT NULL,
  6.       `Qos` CHAR(1)  NOT NULL,
  7.       PRIMARY KEY (`ID`),
  8.       UNIQUE KEY `ID` (`ID`)
  9.     );
  10.     COMMIT;
  11.     INSERT INTO `service` (`ServiceID`, `ID`, `TCID`, `EndTime`, `Qos`) VALUES
  12.       (2004, 9, 'Jacob', '2011-02-04', '1'),
  13.       (2000, 2, 'Jacob', '2011-01-01', '2'),
  14.       (2000, 3, 'Jacob', '2011-01-01', '2'),
  15.       (2001, 4, 'Jacob', '2011-01-01', '2'),
  16.       (2002, 5, 'Jacob', '2011-02-03', '1'),
  17.       (2003, 6, 'Tyler', '2011-01-04', '1'),
  18.       (2003, 7, 'Tyler', '2011-01-04', '1'),
  19.       (2003, 8, 'Tyler', '2011-01-03', '2'),
  20.       (2005, 10, 'Jacob', '2011-02-05', '1'),
  21.       (2006, 11, 'Jacob', '2011-02-04', '2'),
  22.       (2007, 12, 'Jacob', '2011-01-08', '1'),
  23.       (2008, 13, 'Tyler', '2011-02-06', '1'),
  24.       (2009, 14, 'Dylan', '2011-02-08', '1'),
  25.       (2010, 15, 'Dylan', '2011-02-09', '1'),
  26.       (2014, 16, 'Andrew', '2011-01-01', '1'),
  27.       (2013, 17, 'Andrew', '2011-01-01', '1'),
  28.       (2012, 18, 'Andrew', '2011-02-19', '1'),
  29.       (2011, 19, 'Andrew', '2011-02-02', '1'),
  30.       (2015, 20, 'Andrew', '2011-02-01', '1'),
  31.       (2016, 21, 'Andrew', '2011-01-19', '1'),
  32.       (2017, 22, 'Jacob', '2011-01-01', '1'),
  33.       (2018, 23, 'Dylan', '2011-02-03', '1'),
  34.       (2019, 24, 'Dylan', '2011-01-09', '1'),
  35.       (2020, 25, 'Dylan', '2011-01-01', '1'),
  36.       (2021, 26, 'Andrew', '2011-01-03', '1'),
  37.       (2021, 27, 'Dylan', '2011-01-11', '1'),
  38.       (2022, 28, 'Jacob', '2011-01-09', '1'),
  39.       (2023, 29, 'Tyler', '2011-01-19', '1'),
  40.       (2024, 30, 'Andrew', '2011-02-01', '1'),
  41.       (2025, 31, 'Dylan', '2011-02-03', '1'),
  42.       (2026, 32, 'Jacob', '2011-02-04', '1'),
  43.       (2027, 33, 'Tyler', '2011-02-09', '1'),
  44.       (2028, 34, 'Daniel', '2011-01-06', '1'),
  45.       (2029, 35, 'Daniel', '2011-02-01', '1');
  46.      COMMIT;
some of data
Expand|Select|Wrap|Line Numbers
  1. ServiceID    ID    TCID    EndTime        Qos
  2. 2000    2    Jacob    2011-01-01    2
  3. 2000    3    Jacob    2011-01-01    2
  4. 2001    4    Jacob    2011-01-01    2
  5. 2002    5    Jacob    2011-02-03    1
  6. 2003    6    Tyler        2011-01-04    1
  7. 2003    7    Tyler        2011-01-04    1
  8. 2003    8    Tyler        2011-01-03    2
  9. 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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. TCID           level
  2. Andrew       top2
  3. Jacob         top2
  4. Tyler           top4
plz help
Apr 19 '12 #1
Share this Question
Share on Google+
16 Replies


Expert 100+
P: 1,035
aim2:
Expand|Select|Wrap|Line Numbers
  1. set @a:=0; 
  2. select P, tcid, RANK 
  3. from (
  4.     select P, tcid, @a:=CASE WHEN @p=P THEN @a+1 ELSE 1 END  RANK, @p:=P 
  5.     from (select concat(year(EndTime), lpad(format(month(EndTime),0),2,'0')) P, tcid, count(*) C  
  6.     from (
  7.         SELECT DISTINCT serviceid,tcid,endtime,qos 
  8.         FROM (
  9.             SELECT * 
  10.             FROM service 
  11.             ORDER BY serviceid, qos, id) AS base 
  12.         GROUP BY serviceid) X 
  13.     group by EndTime, tcid 
  14.     order by year(EndTime), month(EndTime), C desc )X2 
  15. )X3 where RANK<=2;
outputs:
Expand|Select|Wrap|Line Numbers
  1. +--------+--------+------+
  2. | P      | tcid   | RANK |
  3. +--------+--------+------+
  4. | 201101 | Jacob  |    1 |
  5. | 201101 | Andrew |    2 |
  6. | 201102 | Jacob  |    1 |
  7. | 201102 | Andrew |    2 |
  8. +--------+--------+------+
aim3: This will be more of the same concept, i think.... ;)
Apr 21 '12 #2

P: 19
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:
Expand|Select|Wrap|Line Numbers
  1. month    cnt    TCID    rank
  2. 1    4    Andrew    1
  3. 1    1    Daniel    5
  4. 1    2    Dyla    3
  5. 1    3    Jacob    2
  6. 1    2    Tyler    3
  7. 2    4    Andrew    1
  8. 2    1    Daniel    5
  9. 2    4    Dyla    1
  10. 2    4    Jacob    1
  11. 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.
Apr 23 '12 #3

Expert 100+
P: 1,035
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.
Apr 23 '12 #4

P: 19
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.
Apr 24 '12 #5

Rabbit
Expert Mod 10K+
P: 12,359
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.
Apr 24 '12 #6

P: 19
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.
Apr 24 '12 #7

Rabbit
Expert Mod 10K+
P: 12,359
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT TCID, Class
  2. FROM (
  3.     SELECT T1.TCID, T1.EndMonth, 
  4.         FLOOR(CONVERT(FLOAT, (CASE WHEN T2.EndMonth IS NULL THEN 0 ELSE COUNT(*) END)) / 2.0) + 1 AS Class
  5.     FROM (
  6.         SELECT T.TCID, MONTH(EndTime) AS EndMonth, COUNT(*) AS NumSatisfied
  7.         FROM Service T
  8.             INNER JOIN (
  9.                 SELECT ServiceID, MIN(ID) AS ID
  10.                 FROM Service
  11.                 WHERE Qos = 1
  12.                 GROUP BY ServiceID
  13.             ) MinID
  14.             ON T.ServiceID = MinID.ServiceID
  15.                 AND T.ID = MinID.ID
  16.         GROUP BY T.TCID, MONTH(EndTime)
  17.     ) T1 LEFT JOIN (
  18.         SELECT T.TCID, MONTH(EndTime) AS EndMonth, COUNT(*) AS NumSatisfied
  19.         FROM Service T
  20.             INNER JOIN (
  21.                 SELECT ServiceID, MIN(ID) AS ID
  22.                 FROM Service
  23.                 WHERE Qos = 1
  24.                 GROUP BY ServiceID
  25.             ) MinID
  26.             ON T.ServiceID = MinID.ServiceID
  27.                 AND T.ID = MinID.ID
  28.         GROUP BY T.TCID, MONTH(EndTime)
  29.     ) T2
  30.     ON T1.EndMonth = T2.EndMonth
  31.         AND T1.NumSatisfied < T2.NumSatisfied
  32.     GROUP BY T1.TCID, T1.EndMonth, T2.EndMonth
  33.     HAVING (CASE WHEN T2.EndMonth IS NULL THEN 0 ELSE COUNT(*) END) < 4
  34. ) Final
  35. GROUP BY TCID, Class
  36. HAVING COUNT(*) = 2
  37. ORDER BY Class;
Apr 24 '12 #8

Expert 100+
P: 1,035
@bbaaking
hmm, is your 'fina' goal a MySQL, of a MSSQL solution?
Apr 24 '12 #9

Rabbit
Expert Mod 10K+
P: 12,359
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.
Apr 24 '12 #10

P: 19
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:
Expand|Select|Wrap|Line Numbers
  1. WITH MonthCount AS (SELECT   DATEDIFF(mm, MIN(EndTime), MAX(EndTime)) + 1 AS MonthCountField
  2.                                     FROM      service),
  3. rank_of_person as(
  4.         select 
  5.             [TCID], 
  6.             TheMonth,
  7.             [Personal_Total_Satisfied_Services_this_Month],
  8.             rank() over 
  9.                 (partition by TheMonth order by [Personal_Total_Satisfied_Services_this_Month] desc) R
  10.         from (
  11.             select 
  12.                 [TCID], 
  13.                 month(EndTime) TheMonth,
  14.                 count(*) as [Personal_Total_Satisfied_Services_this_Month]
  15.             from (
  16.                 select *, row_number() over (partition by [ServiceID] order by ID)  RN
  17.                 from [Service]
  18.             ) T
  19.             where RN = 1 and [Qos] = 1
  20.             group by [TCID],month(EndTime)
  21.             --order by month(EndTime)
  22.         ) T
  23. )
  24. select [TCID],'top2'
  25. from (
  26.     select 
  27.         [TCID], 
  28.         TheMonth,
  29.         [Personal_Total_Satisfied_Services_this_Month],
  30.         R
  31.     from rank_of_person
  32.     where R < 3
  33. ) T
  34. group by [TCID]
  35. having count(*) = (select MonthCountField from MonthCount)
  36. union
  37. select [TCID],'top4'
  38. from (
  39.     select 
  40.         [TCID], 
  41.         TheMonth,
  42.         [Personal_Total_Satisfied_Services_this_Month],
  43.         R
  44.     from rank_of_person
  45.     where R > =3 and R<=4
  46. ) T
  47. group by [TCID]
  48. having count(*) = (select MonthCountField from MonthCount)
Apr 25 '12 #11

Rabbit
Expert Mod 10K+
P: 12,359
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.
Apr 25 '12 #12

P: 19
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.
Apr 25 '12 #13

Rabbit
Expert Mod 10K+
P: 12,359
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.
Apr 25 '12 #14

P: 19
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.
Apr 26 '12 #15

P: 19
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?
Apr 26 '12 #16

Rabbit
Expert Mod 10K+
P: 12,359
In the HAVING clause, you just need to get rid of the CASE statement and use COUNT(*) < 4
Apr 26 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.