代码改变世界

PDI的steps:(6:Analytic Query)

2013-05-24 13:48  很大很老实  阅读(371)  评论(0编辑  收藏  举报

Description

This step allows you to peek forward and backwards across rows. Examples of common use cases are:

  • Calculate the "time between orders" by ordering rows by order date, and LAGing 1 row back to get previous order time.
  • Calculate the "duration" of a web page view by LEADing 1 row ahead and determining how many seconds the user was on this page.

Options

The following table provides a description of the options available for the Analytic Query step:

OptionDescription
Step name Name of the step; this name has to be unique in a single transformation
Group fields table Specify the fields over which you want to group. Click Get Fields to add all fields from the input stream(s). The step will do no additional sorting, so in addition to the grouping identified (ie, CUSTOMER_ID) here you MUST also have the data sorted (ORDER_DATE)
Analytic Functions table Specify the analytic functions to be solved.
  • New Field Name - the name you want this new field to be named on the stream (PREV_ORDER_DATE)
  • Subject - The existing field to grab (ORDER_DATE)
  • Type - A type of analytic function
  • N - The number of rows to offset (backwards or forwards)

Here are the available analytic functions :

  • Lead - Go forward N rows and get the value of Subject
  • Lag - Go backward N rows and get the value of Subject |

Examples

These are the examples that are available in our distribution:

samples/transformations/Analytic Query - Lead One Example.ktr
samples/transformations/Analytic Query - Random Value Example.ktr