473,573 Members | 2,889 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

OLAP windowing legerdemain

Friends:

Say I have a simple table like so:

REGION (CHAR(3)
SSN CHAR(9)
ID SMALLINT

With the following data:

REGION SSN ID
------ --------- --
002 555112222 2
008 555112222 8
038 555112222 27
098 111223333 89
016 111223333 14

I'd like to find pair-wise duplicates based on SSN, yielding a result
set like so:

REGION_1 ID_1 SSN REGION_2 ID_2
-------- ---- ------- -------- ----
002 2 555112222 008 8
008 8 555112222 038 27
038 27 555112222 002 2
098 89 111223333 016 14

Now, I could certainly do a self-join (and may yet have to) WHERE ID !=
ID AND SSN = SSN, but this has at least two drawbacks: 1) two passes
through the access path, and 2) I'd get matches and their (redundant)
reciprocals, though on this latter point I'm OK with a little
post-filtering, algorithms for this being available (e.g., those by
Kyte and Molinaro).

What I'm hoping is that among you is someone who knows of a usage of
the OLAP windowing functions for just such an occasion.

My first attempt (below) seems OK for IDs, but generates reciprocals
and will get messy when I try to incoporate the correct corresponding
REGIONs.

WITH
SSNS
(
REGION,
SSN,
ID
)
AS
(
VALUES
('002','5551122 22', 2),
('008','5551122 22', 8),
('038','5551122 22', 27),
('098','1112233 33', 89),
('016','1112233 33', 14)
)
SELECT
ID ID_1,
CASE WHEN ID = ID1 THEN ID2 ELSE ID1 END ID_2
FROM
(
SELECT
ID,
MAX(ID) OVER (PARTITION BY SSN ORDER BY ID ROWS BETWEEN CURRENT ROW
AND 1 FOLLOWING) AS ID1,
MIN(ID) OVER (PARTITION BY SSN ORDER BY ID) AS ID2
FROM
SSNS A
) X

ID1 ID2
----------- -----------
14 89
89 14
2 8
8 27
27 2

Regards,

--Jeff

Oct 9 '06 #1
3 1620

jefftyzzer wrote:
Friends:

Say I have a simple table like so:

REGION (CHAR(3)
SSN CHAR(9)
ID SMALLINT

With the following data:

REGION SSN ID
------ --------- --
002 555112222 2
008 555112222 8
038 555112222 27
098 111223333 89
016 111223333 14

I'd like to find pair-wise duplicates based on SSN, yielding a result
set like so:

REGION_1 ID_1 SSN REGION_2 ID_2
-------- ---- ------- -------- ----
002 2 555112222 008 8
008 8 555112222 038 27
038 27 555112222 002 2
098 89 111223333 016 14
This should do the trick:

with region (REGION, SSN, ID) as (values
('002', '555112222', 2),
('008', '555112222', 8),
('038', '555112222', 27),
('098', '111223333', 89),
('016', '111223333', 14)),
intermediate_re sult (region, id1, ssn, id2, row, id_count) as
(select region, id as id1, ssn, coalesce(max(id ) over(partition by ssn
order by id rows between 1 following and 1 following), min(id)
over(partition by ssn)) as id2, row_number() over(partition by ssn) as
row, sum(1) over(partition by ssn) as id_count
from region
order by ssn, id)
select region, id1, ssn, id2 from intermediate_re sult
where id_count 1 and (id_count 2 or row = 1)
REGION ID1 SSN ID2
====== === === ===
016 14 111223333 89
002 2 555112222 8
008 8 555112222 27
038 27 555112222 2

-Chris

Oct 10 '06 #2
VERY nice! Thanks, Chris--this is a huge help. I appreciate your
spending time on this.

--Jeff

ChrisC wrote:
jefftyzzer wrote:
Friends:

Say I have a simple table like so:

REGION (CHAR(3)
SSN CHAR(9)
ID SMALLINT

With the following data:

REGION SSN ID
------ --------- --
002 555112222 2
008 555112222 8
038 555112222 27
098 111223333 89
016 111223333 14

I'd like to find pair-wise duplicates based on SSN, yielding a result
set like so:

REGION_1 ID_1 SSN REGION_2 ID_2
-------- ---- ------- -------- ----
002 2 555112222 008 8
008 8 555112222 038 27
038 27 555112222 002 2
098 89 111223333 016 14

This should do the trick:

with region (REGION, SSN, ID) as (values
('002', '555112222', 2),
('008', '555112222', 8),
('038', '555112222', 27),
('098', '111223333', 89),
('016', '111223333', 14)),
intermediate_re sult (region, id1, ssn, id2, row, id_count) as
(select region, id as id1, ssn, coalesce(max(id ) over(partition by ssn
order by id rows between 1 following and 1 following), min(id)
over(partition by ssn)) as id2, row_number() over(partition by ssn) as
row, sum(1) over(partition by ssn) as id_count
from region
order by ssn, id)
select region, id1, ssn, id2 from intermediate_re sult
where id_count 1 and (id_count 2 or row = 1)
REGION ID1 SSN ID2
====== === === ===
016 14 111223333 89
002 2 555112222 8
008 8 555112222 27
038 27 555112222 2

-Chris
Oct 10 '06 #3
To display Region2, it will be better to JOIN Regin table. This also
remove unneccesary rows.

-------------------- Commands Entered ------------------------------
WITH region (REGION, SSN, ID) as (values
('002', '555112222', 2),
('008', '555112222', 8),
('038', '555112222', 27),
('098', '111223333', 89),
('016', '111223333', 14)
)
SELECT region_1, id_1, S2.ssn, R.region AS region_2, id_2
FROM (SELECT region_1, id_1, ssn
, NULLIF(COALESCE (idf,idmin),idp ) AS id_2
FROM (SELECT region AS region_1, id AS id_1, ssn
, MAX(id) OVER(PARTITION BY ssn ORDER BY id
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING) AS idf
, MAX(id) OVER(PARTITION BY ssn ORDER BY id
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS idp
, MIN(id) OVER(PARTITION BY ssn) AS idmin
FROM region
) S1
) S2
INNER JOIN
region R
ON R.id = S2.id_2
AND R.ssn = S2.ssn
;
---------------------------------------------------------------------
REGION_1 ID_1 SSN REGION_2 ID_2
-------- ----------- --------- -------- -----------
016 14 111223333 098 89
002 2 555112222 008 8
008 8 555112222 038 27
038 27 555112222 002 2

4 record(s) selected.

Oct 18 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
3562
by: Will | last post by:
On the subject of Data Warehouses, Data Cubes & OLAP…. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else that these buzz words were created by some geek who decided to take a stab at marketing? Knowing that to the backwoods manager who knows little of...
0
3351
by: DD | last post by:
Hi Guys! Just would like to share with you my experiense in this matter. I was trying to evaluate how suitable Oracle OLAP for our applications. As probably you did, I have downloaded from OTN website OraJDeveloper and BI Beans, installed them and opened Cube Viewer ... no luck! Just did not work. After very long conversation(s) with...
5
2441
by: Framework fan | last post by:
Hello, If I wrote the next ebay (yes I know, yawn-snore) and I had a database with 5 million auction items in it, what would be a really good strategy to get a search done very quickly? Would it involve something called OLAP and/or "data mining"? The only technology I am familiar with is simply SQL Server databases with stored procedures....
2
5221
by: Gadi Refaeli | last post by:
Hello All, We are currently considering DB purchases for a new system, we are looking at Cognos, Oracle, DB2 and Essbase. We came across some questions regarding Essbase and DB2 OLAP. 1. Are DB2 OLAP and Essbase are identical? 2. Does the standard Essbase SDK is provided with DB2 OLAP? 3. Does EIS is provided with DB2 OLAP? 4. Is there...
3
2739
by: Eduardo Quiroz Salinas | last post by:
do someone knows where can i get a good tutorial or how to make OLAP cubes.???? thanx a lot -- Linux user number 344659 "...Los que no requieren de un dios para ser virtuosos, son la desesperacion de los creyentes..."
3
5829
by: George Sakkis | last post by:
After a brief search, I didn't find any python package related to OLAP and pivot tables. Did I miss anything ? To be more precise, I'm not so interested in a full-blown OLAP server with an RDBMS backend, but rather a pythonic API for constructing datacubes in memory, slicing and dicing them, drilling down or up dimensions and exposing them in...
0
1671
by: YellowFin Announcements | last post by:
Yellowfin Reporting Announces Release 3 OLAP Connectivity New Features Including OLAP-to-Relational Drill Through Provide Customers with One Complete Web BI Tool for OLAP Analysis Yellowfin, today announced the availability of Yellowfin Release 3, the newest version of the leading query, reporting, and analysis tool for the web. Release 3...
0
1520
by: YellowFin | last post by:
Yellowfin Announces Release 3 OLAP Connectivity New Features Including OLAP-to-Relational Drill Through Provide Customers with One Complete Web BI Tool for OLAP Analysis Yellowfin, today announced the availability of Yellowfin Release 3, the newest version of the leading query, reporting, and analysis tool for the web. Release 3 provides...
0
1958
by: jefftyzzer | last post by:
Friends: In v8 or v9 LUW, if I were to write (in Java or C) my own user-defined aggregate function, would I be able to use it in an OLAP windowing clause as I could use, e.g., COUNT or SUM? Example: SELECT JEFFS_UDAF() OVER(PARTITION BY SOME_COL)
0
7755
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7992
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8190
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8048
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6385
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5281
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3722
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2183
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1284
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.