comment on

Thanks for the offer of extra eyes - I am in fact somewhat databatially challenged.

The goal is to take a list of real events and compare this with a list of potential events in order to do a statistical analysis of the probability of events occurring.

The two tables are created like this:

CREATE TABLE Real_Events (
    Id          INT,
    Name1    VARCHAR,
    Name2    VARCHAR,
    Date        INT,
    Group    VARCHAR,
)
CREATE TABLE Potential_Events (
    Name1    VARCHAR,
    Name2    VARCHAR,
    Group    VARCHAR,
)
[download]

The SELECTs I need are, for a given event in Real_Events:

all the real events in the same Group with occur later, but not more than a certain number of days later. e.g.

  SELECT * from Events WHERE  Group="physicists" AND Date>$event_date 
+AND
  Date<=($event_date + $window))
[download]

all the events in Potential_Events in the same Group with all combinations of Names found in the previous SELECT, e.g. if a real Alice-Bob event and a real Bob-Charlie event were found, I would then have
```
  SELECT * from Potential_Events WHERE
  Group="physicists" AND ( ( Name1="Alice" AND   Name2="Bob" ) OR (
  Name1="Alice" AND  Name2="Charlie" ) OR ( Name1="Bob" and  Name2="Ch
+arlie" ) )
[download]
```
All potential events found then have to be checked against real events. If a potential event is found in the table of real events, it is discarded if has already taken place relative to the date of the real event being considered. So for a given potential event I have, say,
```
  SELECT * from Events WHERE Group="physicists" AND  Name1="Alice" AND
  Name2="Bob" AND Date>$event_date)
[download]
```

I currently read the entire table of actual events into an array of hashrefs. This allows me to define a look-up table on, say, 'Group':

  push (@{$lookup_group{$_->{"Group"}}}, $_) for @rows;
[download]

I can use this to get the rows for a given group and then loop over these to check the date. A similar approach can be used to create a look-up table with "$Name1$Name2$Group" as the key, which can be used to check whether a potential event has already occured

I hope that gives a rough idea of what I'm trying to do. The program is orginally from a user whom I am trying to help get his data crunched before a deadline. So optimisation time plus run time has to comply with this restriction. Apart from that, unfortunately I also have other work to do, so the time I have to work on this also limited, but I would be greatful for any pointers to low-hanging fruit.

Thanks,

loris

In reply to Re^6: Creating SELECT-like look-up tables as hashes by loris
in thread Creating SELECT-like look-up tables as hashes by loris

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.