Aggregate MODE function (most frequent date)
Hugo Zambrano wanted a MODE aggregate returning the most frequent date per name, plus an average score. Paul Watson and Art Kagel suggested count with ordering, SPL or temp tables; Hugo wanted to avoid SPL. SangGyu Jeong supplied a ROW_NUMBER() OVER (PARTITION BY ...) query and later improved variants, including one from a Korean community member. Hugo adapted it and it worked.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi Everybody. I am looking for an aggregate function that implements the "mode" of a list of dates. The mode is the number (in my case a date) that occur the most frequently. Does anybody already have one that do that? ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------ #Informix
Did I miss something, happens often, but ....
select datecol, count(*) from XX group 1 order 2 desc????
Cheers
Paul
> ???Hi Everybody.
>
> I am looking for an aggregate function that implements the "mode" of a
> list of dates.
> The mode is the number (in my case a date) that occur the most frequently.
> Does anybody already have one that do that?
>
> ------------------------------
> Zambrano, Hugo
> Informix DBA
> Ottawa Police
> Ottawa, ON
> (613)236-1222, 5575
> ------------------------------
>
>
> Reply to Sender :
>
https://community.ibm.com/eGroups/PostReply/?GroupId=4147&SenderKey=7e85680a-630b-40bd-809c-c032f75d9cd2&MID=46818&MDATE=7575458469&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved
>
> Reply to Discussion :
>
https://community.ibm.com/eGroups/PostReply/?GroupId=4147&MID=46818&MDATE=7575458469&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved
>
>
>
> You are subscribed to "Informix" as
paul@oninit.com
. To change your
> subscriptions, go to
>
http://community.ibm.com/community/user/preferences?section=Subscriptions&MDATE=7575458469&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved.
> To unsubscribe from this community discussion, go to
>
http://community.ibm.com/HigherLogic/eGroups/Unsubscribe.aspx?UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved&GroupKey=60eb97a2-57c0-4130-9b6d-57174f97d5a8.
--
Paul Watson
Tel: +1 913-674-0360
Mob: +1 913-387-7529
Web:
www.oninit.com
Oninit? is a registered trademark of Oninit LLC
Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.
What this country needs are more unemployed politicians
This what I want:
SELECT Name, MODE(dob), AVG(score) FROM tables GROUP BY NameThis considering that there are several people with same name but different DOB.
Using your SELECT statement requires multiple steps in order to select the first record of each group.
------------------------------
Zambrano, Hugo
Informix DBA
Ottawa Police
Ottawa, ON
(613)236-1222, 5575
------------------------------
SELECT FIRST 1 date, count(*)
FROM mytable
ORDER BY 2 DEC;You can make it a subquery derived table from which you can return just the date and poof!
Not hard to make this a dynamic query in an SPL function that takes table and column as arguments.
Art
like this?
SELECT Name,
dob,avg
FROM (SELECT Name,
dob,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY COUNT(*) DESC) rn,
AVG(score) OVER (PARTITION BY Name) avg
FROM tables
GROUP BY Name, dob, score
) a
WHERE rn =1
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Thanks a lot SangGuy, This is new to me. I didn't know this existed. This gives me results really close to what I am looking for; however, I want to improve it a little more: When the members of a group of names all have a different DOB I want to choose one DOB within the most common year-month. For example, let's say I have the following: JESSE 1992/10/27 JESSE 1992/10/27 JESSE 1992/11/06 JESSE 1992/11/11 JESSICA 1992/03/11 JESSICA 1992/11/03 JESSICA 1992/10/29 JESSICA 1992/11/10 JESSICA 1992/11/30 JESSICA 1992/11/12 JESSICA 1992/12/07 JESSICA 1992/12/09 My output should be: JESSE 1992/10/27 (as it has the most common DOB) JESSICA 1992/11/?? (Any date in 1992/11 as 11 is the most common month) I have tried modifying your OLAP statement without success. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
As Art suggested doing it in SPL and the use a derived table, or do it via
a temp table
> Thanks a lot SanGuy,
>
> This is new to me. I didn't know this existed.
>
> This gives me results really close to what I am looking for; however, I
> want to improve it a little more: When the members of a group of names all
> have a different DOB I want to choose one DOB within the most common
> year-month. For example, let's say I have the following:
>
> JESSE 1992/10/27
> JESSE 1992/10/27
> JESSE 1992/11/06
> JESSE 1992/11/11
> JESSICA 1992/03/11
> JESSICA 1992/11/03
> JESSICA 1992/10/29
> JESSICA 1992/11/10
> JESSICA 1992/11/30
> JESSICA 1992/11/12
> JESSICA 1992/12/07
> JESSICA 1992/12/09
>
> My output should be:
> JESSE 1992/10/27 (as it has the most common DOB)
> JESSICA 1992/11/?? (Any date in 1992/11 as 11 is the most common month)
>
> I have tried modifying your OLAP statement without success.
>
> ------------------------------
> Zambrano, Hugo
> Informix DBA
> Ottawa Police
> Ottawa, ON
> (613)236-1222, 5575
> ------------------------------
> -------------------------------------------
> Original Message:
> Sent: Sun March 15, 2020 12:46 AM
> From: SangGyu Jeong
> Subject: Aggregate MODE function (most frequent date)
>
> like this?
>
> SELECT Name, dob, avg FROM (SELECT Name, dob,
> ROW_NUMBER() OVER (PARTITION BY Name ORDER BY COUNT(*) DESC)
> rn, AVG(score) OVER (PARTITION BY Name) avg FROM> tables GROUP BY Name, dob, score ) a WHERE rn =1
>
> ------------------------------
> SangGyu Jeong
> Software Engineer
> Infrasoft
> Seoul Korea, Republic of
> ------------------------------
>
> Original Message:
> Sent: Sat March 14, 2020 06:20 PM
> From: Hugo Zambrano
> Subject: Aggregate MODE function (most frequent date)
>
> This what I want:
>
> SELECT Name, MODE(dob), AVG(score) FROM tables GROUP BY Name>
> This considering that there are several people with same name but
> different DOB.
>
> Using your SELECT statement requires multiple steps in order to select the
> first record of each group.
>
>
> ------------------------------
> Zambrano, Hugo
> Informix DBA
> Ottawa Police
> Ottawa, ON
> (613)236-1222, 5575
>
> Original Message:
> Sent: Sat March 14, 2020 01:58 PM
> From: Paul Watson
> Subject: Aggregate MODE function (most frequent date)
>
> Did I miss something, happens often, but ....
>
> select datecol, count(*) from XX group 1 order 2 desc>
> ????
>
> Cheers
> Paul
>
>> ???Hi Everybody.
>>
>> I am looking for an aggregate function that implements the "mode" of a
>> list of dates.
>> The mode is the number (in my case a date) that occur the most
>> frequently.
>> Does anybody already have one that do that?
>>
>> ------------------------------
>> Zambrano, Hugo
>> Informix DBA
>> Ottawa Police
>> Ottawa, ON
>> (613)236-1222, 5575
>> ------------------------------
>>
>>
>> Reply to Sender :
>>
https://community.ibm.com/eGroups/PostReply/?GroupId=4147&SenderKey=7e85680a-630b-40bd-809c-c032f75d9cd2&MID=46818&MDATE=7575458469&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved
>>
#Informix
Hugo:
It's not very sophisticated, but I hope it helps.
The WITH statement is for use as sample data and can be ignored.
Please point out if there is something wrong.
[informix@db2 ids1410fc3]$ cat mode.sql
WITH t1 AS
(
SELECT 'JESSE' Name, '1992/10/27' dob, 10 score
UNION ALL SELECT 'JESSE', '1992/10/27', 20
UNION ALL SELECT 'JESSE', '1992/11/06', 30
UNION ALL SELECT 'JESSE', '1992/11/11', 40
UNION ALL SELECT 'JESSICA', '1992/03/11', 50
UNION ALL SELECT 'JESSICA', '1992/11/03', 60
UNION ALL SELECT 'JESSICA', '1992/10/29', 70
UNION ALL SELECT 'JESSICA', '1992/11/10', 80
UNION ALL SELECT 'JESSICA', '1992/11/30', 90
UNION ALL SELECT 'JESSICA', '1992/11/12', 10
UNION ALL SELECT 'JESSICA', '1992/12/07', 20
UNION ALL SELECT 'JESSICA', '1992/12/09', 30
)
SELECT a.Name,
a.dob,a.avg
FROM (SELECT t1.Name,
t1.dob,
ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rn,
AVG(t1.score) OVER (PARTITION BY t1.Name) avg
FROM t1,
(SELECT t1.Name,
t1.dob[1,7],
RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
FROM t1
GROUP BY t1.Name, t1.dob[1,7]
) t2
WHERE t2.rk = 1
AND t1.Name = t2.Name
AND t1.dob like t2.dob||'%'
GROUP BY t1.Name, t1.dob, t1.score
) a
WHERE rn = 1
[informix@db2 ids1410fc3]$ dbaccess stores_demo mode.sql
Database selected.
name dob avg
JESSE 1992/10/27 25.0000000000000
JESSICA 1992/11/30 60.0000000000000
2 row(s) retrieved.
Database closed.
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Hi Art, Thanks for your suggestion. I want to avoid SPLs. I had tried the method you are suggesting without any success. I can get it with a complicate steps of multiple temporary tables. The problems using your suggestion are, first I cannot use a FIRST 1 in a derived table, and second if that were possible, I could not get the FIRST 1 per group. So far SangGyu solution seem to give me the result I need. If you have a chance perhaps you can give more details of how that could be archived. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
Thanks a lot. I adapted your solution to my specific needs and works pretty well. Thanks for taking the time to help me. I also learned something new too. I'll read more about these OLAP way of doing things. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
Hugo: I'm glad my SQL helped. I'm also not good at using OLAP functions, so I have to search on Google or emulate the methods of SQL experts from other database communities in Korea. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
Hi Hugo,
My crude query was modified with the help of the Korean database community and I would like to share it.
The two queries below are mine and community member manon94's(nickname).
Below are the modifications to the existing query.
1) The average score is calculated based on the name and 'year / month'. The previous one was averaged based only on that name.
2) When the most values are the same, the priority is the earliest date.
Compare the results and choose the one with better performance!
--- my Query (2nd version)
WITH t1 AS
(
SELECT 'JESSE' Name , '1992/08/06' dob, 30 score
UNION ALL SELECT 'JESSE', '1992/08/06', 40
UNION ALL SELECT 'JESSE', '1992/10/27', 10
UNION ALL SELECT 'JESSE', '1992/10/27', 20
UNION ALL SELECT 'JESSE', '1992/11/06', 30
UNION ALL SELECT 'JESSE', '1992/11/11', 40
UNION ALL SELECT 'JESSICA', '1992/03/11', 50
UNION ALL SELECT 'JESSICA', '1992/11/03', 60
UNION ALL SELECT 'JESSICA', '1992/10/29', 70
UNION ALL SELECT 'JESSICA', '1992/11/10', 80
UNION ALL SELECT 'JESSICA', '1992/11/30', 90
UNION ALL SELECT 'JESSICA', '1992/11/12', 10
UNION ALL SELECT 'JESSICA', '1992/12/07', 20
UNION ALL SELECT 'JESSICA', '1992/12/09', 30
)
SELECT a.Name,
a.dob,a.avg
FROM (SELECT t1.Name,
t1.dob,
ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rn,
AVG(t1.score) OVER (PARTITION BY t1.Name, t1.dob[1,7] ) avg
FROM t1,
(SELECT t1.Name,
t1.dob[1,7],
RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
FROM t1
GROUP BY t1.Name, t1.dob[1,7]
) t2
WHERE t2.rk = 1
AND t1.Name = t2.Name
AND t1.dob like t2.dob||'%'
GROUP BY t1.Name, t1.dob, t1.score
) a
WHERE rn =1
--- manon94's Query
WITH t1 AS
(
SELECT 'JESSE' Name , '1992/08/06' dob, 30 score
UNION ALL SELECT 'JESSE', '1992/08/06', 40
UNION ALL SELECT 'JESSE', '1992/07/27', 10
UNION ALL SELECT 'JESSE', '1992/07/27', 20
UNION ALL SELECT 'JESSE', '1992/11/06', 30
UNION ALL SELECT 'JESSE', '1992/11/11', 40
UNION ALL SELECT 'JESSICA', '1992/03/11', 50
UNION ALL SELECT 'JESSICA', '1992/11/03', 60
UNION ALL SELECT 'JESSICA', '1992/10/29', 70
UNION ALL SELECT 'JESSICA', '1992/11/10', 80
UNION ALL SELECT 'JESSICA', '1992/11/30', 90
UNION ALL SELECT 'JESSICA', '1992/11/12', 10
UNION ALL SELECT 'JESSICA', '1992/12/07', 20
UNION ALL SELECT 'JESSICA', '1992/12/09', 30
)
SELECT *
FROM (SELECT name
, dob
, avg_m
, ROW_NUMBER() OVER(PARTITION BY name ORDER BY cnt_m DESC, cnt_d DESC, dob) rn
FROM (SELECT name
, dob
, COUNT(*) OVER(PARTITION BY name, dob[1,7]) cnt_m
, COUNT(*) OVER(PARTITION BY name, dob ) cnt_d
, AVG(score) OVER(PARTITION BY name, dob[1,7]) avg_m
FROM t1
) a
) a
WHERE rn = 1
;
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Thanks a lot !! I understand what you mean, and I now realized I could use this syntax in other areas of my program. I am going to experiment a little with all this; for instance, I wonder if FIRST_VALUE( ) could be used instead of ROW_NUMBER( ). ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
@Hugo Zambrano Since the FIRST_VALUE function is to display the first value of partitioned window, I don't think it is suitable for displaying only the most frequent values. Sorry if the answer is different from the intention of the question. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
I like manon94's solution too: I can see that is simpler than the original: Since the COUNT(*) is outside the PARTITION clause the GROUP BY is not required, and also the JOIN is not required as all data is already coming from the inner table, ... and as an exercise, I was also able to modified it to get the AVG date of the most frequent month Thanks again. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------