51128

Implement aggregation in Teradata

Question:

I want to aggregate 2 fields proct_dt, dw_job_id in ascendinng order My scenario would be clear by using below queries and result.

First query :-

sel * from scratch.COGIPF_RUNREPORT_test1 order by proct_dt,dw_job_id where dw_job_id =10309

Output :-

dw_job_id proct_dt start_ts end_ts time_diff

1 10,309 2018-03-06 00:00:00 2018-03-06 07:04:18 2018-03-06 07:04:22.457000 0<br /> 2 10,309 2018-03-06 00:00:00 2018-03-06 06:58:50 2018-03-06 06:58:51.029000 0<br /> 3 10,309 2018-03-07 00:00:00 2018-03-07 06:35:36 2018-03-07 06:36:03.809000 1<br /> 4 10,309 2018-03-06 00:00:00 2018-03-06 07:00:35 2018-03-06 07:00:40.702000 0

5 10,309 2018-03-06 00:00:00 2018-03-06 06:30:25 2018-03-06 06:30:42.759000 0

6 10,309 2018-03-06 00:00:00 2018-03-06 07:10:27 2018-03-06 07:10:28.715000 0

7 10,309 2018-03-06 00:00:00 2018-03-06 06:59:44 2018-03-06 06:59:48.315000 0

8 10,309 2018-03-06 00:00:00 2018-03-06 07:00:15 2018-03-06 07:00:15.086000 0

9 10,309 2018-03-06 00:00:00 2018-03-06 07:04:02 2018-03-06 07:04:02.925000 0

2nd Query :-

sel * from scratch.fact_test order by proct_dt asc ,dw_job_id asc where dw_job_id =10309

Result :-

dw_job_id proct_dt start_ts end_ts status

1 10,309 2018-03-06 00:00:00 2018-03-06 06:30:25 2018-03-06 06:30:42.759 12

2 10,309 2018-03-07 00:00:00 2018-03-07 06:35:36 2018-03-07 06:36:03.809 12

So here in the 2nd query I got the desire result as from 1st table by having first occurrence of proct_dt,start_ts,end_ts

Please let me know for any clarification It would be very great if anyone can help to achieve this case.

Thanks,

Answer1:

It's not clear what you want from your explanation, but it looks like you want to get the first run of a job per day, which is easy using a Row_Number:

select * from scratch.COGIPF_RUNREPORT_test1 where dw_job_id =10309 qualify row_number() over (partition by dw_job_id, proc_dt -- for each job & date order by start_ts) = 1 -- only the 1st run

Recommend

  • Convert string to time in php
  • Creating a setup.exe in InstallShield in Visual Studio
  • OpenMP and File I/O
  • Real Time CountDown Timer In Python
  • matching similar elements in between two lists
  • Prolog Ambiguous Output
  • Changing a global variable in C
  • Detecting # in Scheme list
  • Interpreting STRACE output - pipes and forks
  • matlab crashes without dump file when using fopen for file
  • is there a way to update filter with async data
  • Why people use prototype in javascript when it is easy to inherit using apply () and call () methods
  • Is it possible to get the word under the mouse cursor in a ``?
  • Cannot save model when using ember render helper
  • Write output of for loop to multiple files
  • Getting different value with placeholder over CPU/GPU
  • there is no graph with tensorboard
  • Azure webjobs output logs indexing taking very long
  • Python ImageIO Gif Set Delay Between Frames
  • Group list of tuples by item
  • Scipy Leastsq Optional Output Variable (Mesg)
  • Trying to get the char code of ENTER key
  • Dynamically switching connect in Modelica
  • calculate gradient output for Theta update rule
  • Access variable of ScriptContext using Nashorn JavaScript Engine (Java 8)
  • Problem while Building a Setup Project for a windows Service?
  • How to attach a node.js readable stream to a Sendgrid email?
  • Functions in global context
  • Unity3D & Android: Difference between “UnityMain” and “main” threads?
  • Breeze - Deleted Items nav properties bug
  • Why value captured by reference in lambda is broken? [duplicate]
  • javaw.exe and eclipse startup problems
  • output of program is not same as passed argument
  • Modifying destination and filename of gulp-svg-sprite
  • Deserializing XML into class C#
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • python draw pie shapes with colour filled
  • How to Embed XSL into XML