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.
Original Message:
Sent: Sat March 21, 2020 12:49 PM
From: SangGyu Jeong
Subject: Aggregate MODE function (most frequent date)
@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
------------------------------
Original Message:
Sent: Sat March 21, 2020 12:05 PM
From: Hugo Zambrano
Subject: Aggregate MODE function (most frequent date)
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
Original Message:
Sent: Wed March 18, 2020 08:51 PM
From: SangGyu Jeong
Subject: Aggregate MODE function (most frequent date)
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 scoreUNION ALL SELECT 'JESSE', '1992/08/06', 40UNION ALL SELECT 'JESSE', '1992/10/27', 10UNION ALL SELECT 'JESSE', '1992/10/27', 20UNION ALL SELECT 'JESSE', '1992/11/06', 30UNION ALL SELECT 'JESSE', '1992/11/11', 40UNION ALL SELECT 'JESSICA', '1992/03/11', 50UNION ALL SELECT 'JESSICA', '1992/11/03', 60UNION ALL SELECT 'JESSICA', '1992/10/29', 70UNION ALL SELECT 'JESSICA', '1992/11/10', 80UNION ALL SELECT 'JESSICA', '1992/11/30', 90UNION ALL SELECT 'JESSICA', '1992/11/12', 10UNION ALL SELECT 'JESSICA', '1992/12/07', 20UNION 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 QueryWITH t1 AS(SELECT 'JESSE' Name , '1992/08/06' dob, 30 scoreUNION ALL SELECT 'JESSE', '1992/08/06', 40 UNION ALL SELECT 'JESSE', '1992/07/27', 10UNION 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
Original Message:
Sent: Mon March 16, 2020 04:08 PM
From: Hugo Zambrano
Subject: Aggregate MODE function (most frequent date)
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
Original Message:
Sent: Sun March 15, 2020 10:27 PM
From: SangGyu Jeong
Subject: Aggregate MODE function (most frequent date)
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.sqlWITH t1 AS(SELECT 'JESSE' Name, '1992/10/27' dob, 10 scoreUNION ALL SELECT 'JESSE', '1992/10/27', 20UNION ALL SELECT 'JESSE', '1992/11/06', 30UNION ALL SELECT 'JESSE', '1992/11/11', 40UNION ALL SELECT 'JESSICA', '1992/03/11', 50UNION ALL SELECT 'JESSICA', '1992/11/03', 60UNION ALL SELECT 'JESSICA', '1992/10/29', 70UNION ALL SELECT 'JESSICA', '1992/11/10', 80UNION ALL SELECT 'JESSICA', '1992/11/30', 90UNION ALL SELECT 'JESSICA', '1992/11/12', 10UNION ALL SELECT 'JESSICA', '1992/12/07', 20UNION 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.sqlDatabase selected.name dob avgJESSE 1992/10/27 25.0000000000000JESSICA 1992/11/30 60.00000000000002 row(s) retrieved.Database closed.
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
Original Message:
Sent: Sun March 15, 2020 07:39 PM
From: Hugo Zambrano
Subject: Aggregate MODE function (most frequent date)
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
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
>
> 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
Original Message------
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