Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 3381 invoked from network); 13 Sep 2007 05:19:03 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 13 Sep 2007 05:19:03 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C60F67422BE; Thu, 13 Sep 2007 05:40:20 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20365-06; Thu, 13 Sep 2007 05:40:20 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9A24E7421A9; Thu, 13 Sep 2007 05:40:18 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 13 Sep 2007 04:55:18 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E986E74155C for ; Thu, 13 Sep 2007 04:55:17 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07226-03-20 for ; Thu, 13 Sep 2007 04:55:17 -0400 (EDT) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4F905740770 for ; Thu, 13 Sep 2007 04:55:16 -0400 (EDT) Received: by nf-out-0910.google.com with SMTP id 4so374186nfv for ; Thu, 13 Sep 2007 02:33:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=qmtOUFy++buyn/lIffihINpzqOb63FQahljV4RfmG+I=; b=NsJjvlXbGN+I20uAII3oTrBiAWp452hpWDa42YHGFQPN/KdN1OOtC3zHpOWFqNlKdk3mISmnueaMDw77N8Ds4yXNKvRKOEnwDhnTQm9desQsxK9YZVNT5hzHiS8kOU6zFrZz0Rse3jOMMjV6ouqW6/bnKOPdN/o0lVwPtUWLLes= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=TOc7tEkL2GMyeo/ESLetxuVxeUY78x6eiXvJ81I9pQXsDNAcs5dhenMlPJpZej3xoNCu4ccItuQUAEA/SdI/N7pfwvIA9S1KuGLBZk4K0sLxmm0zWpJKlY6UZaRJf1r+qWfrmRKQSIC7Xg0nmTjqdl8BC/PYla3W9ezFojzMYCA= Received: by 10.86.58.3 with SMTP id g3mr409332fga.1189676038312; Thu, 13 Sep 2007 02:33:58 -0700 (PDT) Received: by 10.86.52.3 with HTTP; Thu, 13 Sep 2007 02:33:58 -0700 (PDT) Message-ID: <85c1fb130709130233y41addaf9yfd28798ef95aaba1@mail.gmail.com> Date: Thu, 13 Sep 2007 11:33:58 +0200 From: amonte To: oracle-l Subject: Analytics windowing wuth lag/lead? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2171_23200483.1189676038306" X-archive-position: 1500 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: ax.mount@gmail.com Precedence: normal Reply-to: ax.mount@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_2171_23200483.1189676038306 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all I have a query which returns following results SELECT COD_EJER, ID_COMP, VAL, VERSIONING FROM COMP_CL WHERE COD_EJER IN (200202, 200203) AND ID_COMP = 1173 AND KEY = 56 COD_EJER ID_COMP VAL VERSIONING ------------ ---------- ---------- ----------- 200202 1173 22000 1 200202 1173 23000 2 200203 1173 30000 1 200203 1173 30000 3 And teh requirement is: VAL OF MAX(VERSIONING) of 200203 - VAL OF MAX(VERSIONING) of 200202 So it would return folloging COD_EJER ID_COMP VAL VERSIONING DIFF ------------ ---------- ---------- ----------- ----- 200203 1173 30000 3 7000 I know I can do first a MAX(VERSIONING) then filter the max version rows and use lag to subtract but I wonder if there are any better ways such as using windowing? I am basically treating quarters (200201, 200202, 200203 200204) Cheers Alex ------=_Part_2171_23200483.1189676038306 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

Hi all

I have a query which returns following results

SELECT
 COD_EJER,
 ID_COMP,
 VAL,
 VERSIONING
FROM COMP_CL
WHERE COD_EJER IN (200202, 200203)
  AND ID_COMP =  1173
  AND KEY = 56

    COD_EJER    ID_COMP VAL        VERSIONING
------------ ---------- ---------- -----------
      200202       1173 22000                1
      200202       1173 23000                2
      200203       1173 30000                1
      200203       1173 30000                3

And teh requirement is:

VAL OF MAX(VERSIONING) of 200203 - VAL OF MAX(VERSIONING) of 200202

So it would return folloging

    COD_EJER    ID_COMP VAL         VERSIONING   DIFF
------------ ---------- ---------- -----------  -----
      200203       1173 30000                3   7000

I know I can do first a MAX(VERSIONING) then filter the max version rows and use lag to subtract but I wonder if there are any better ways such as using windowing?

I am basically treating quarters (200201, 200202, 200203 200204)

Cheers

Alex
 
------=_Part_2171_23200483.1189676038306-- -- http://www.freelists.org/webpage/oracle-l