Archives of the TeradataForum
Message Posted: Tue, 19 Jan 2010 @ 19:51:25 GMT
<-- Anonymously Posted: Tuesday, January 19, 2010 12:49 -->
I have two tables and a requirement as follows. I'll try to be as brief as possible, hopefully my explanation makes sense.
ID_USER - user id (not important in the calculation) ID_PROD - product id MINUTE_STARTING - a minute of a day when user logged on to a given product (1-1440) MINUTE_ENDING - a minute of a day when user logged off (1-1440) SEC_FIRST_MIN - a second of the first minute when user logged on to a given product (0-60) SEC_LAST_MIN - a second of the last minute when user logged off (0-60)
ID_MINUTE (values: 1-1440) : each ID corresponds to a one-minute slice of a day (24 hours = 1440 minutes)
The requirement is to calculate a SUM of seconds each product was in use within every minute-slice of a day.
The solution proposed by the author of this idea is similiar to this:
SELECT A.ID_PROD , R.ID_MINUTE , SUM(case when R.ID_MINUTE = A.MINUTE_STARTING THEN A.SEC_FIRST_MIN when R.ID_MINUTE = A.MINUTE_ENDING THEN A.SEC_LAST_MIN else 60 end) NB_SECONDS_SLICE FROM Table A A , Ref_table R WHERE R.ID_MINUTE between A.MINUTE_STARTING and A.MINUTE_ENDING GROUP BY A.ID_PROD , R.ID_MINUTE ;
Of course, it necessarily causes a product join. As Table A can have up to 150 million rows, this is not acceptable. Unfortunately, this issue fell on my shoulders and I need to resolve it. I must admit that I am running out of ideas, as the product join somehow makes sense in order to achieve the goal. So if someone can help, I would appreciate very much.
Thanks in advance
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|