agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFind inconsistencies in data with date range
5+ messages / 4 participants
[nested] [flat]
* Find inconsistencies in data with date range
@ 2015-03-06 21:38 Jason Aleski <jason.aleski@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Jason Aleski @ 2015-03-06 21:38 UTC (permalink / raw)
To: pgsql-sql
I know I can do this Java, but I'd rather have this running as a Stored
Procedure. What I am wanting to do is identify and potentially correct
the summary data for date inconsistencies. We have policies/red flag
reports in place to keep this from happening, but we are now cleaning up
history. The query below works on a per store basis, but I'd like to be
able to run this for all stores in the location table.
I've looked at some procedure codes regarding looping, but everything I
try to create seems to give me problems. THe code I'm trying is also
below. Does anyone have any suggestions on how to accomplish this?
_Working Tables_
locations - table contains store information, startup date, address, etc
daily_salessummary - table holds daily sales summary by store
(summary should be updated nightly). eod_ts is End of Day Timestamp.
_Query_
WITH datelist AS(
SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek
FROM (SELECT date as GenDate
FROM
generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
day'::interval) date
) AS t1
)
SELECT gendate FROM datelist AS t1
WHERE gendate NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1
JOIN locations AS t2 ON t1.location_id = t2.row_id
WHERE t2.locationCode = 'US_FL_TAMPA_141')
AND gendate > (SELECT start_date FROM locations WHERE locationCode =
'US_FL_TAMPA_141')
_Desired Output_ - could output to an exceptions table
StoreCode 'US_FL_TAMA_141' missing daily summary for 1998-01-01
StoreCode 'MX_OAXACA_SALINA_8344' missing daily summary for 2011-06-05
_ProcedureSQL_ (contains unknown errors)
DECLARE
CURSOR location_table IS
SELECT locationCode FROM locations;
BEGIN
FOR thisSymbol IN ticker_tables LOOP
EXECUTE IMMEDIATE 'WITH datelist AS(
SELECT t1.GenDate as
gendate, extract(dow from GenDate) AS dayofweek
FROM (SELECT date as
GenDate
FROM
generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
day'::interval) date
) AS t1
)
SELECT gendate FROM
datelist AS t1
WHERE gendate NOT IN
(SELECT t1.eod_ts FROM daily_salessummary AS t1
JOIN locations AS t2 ON t1.location_id = t2.row_id
WHERE t2.locationCode = '' || location_table.locationCode || '')
AND gendate > (SELECT
start_date FROM locations WHERE locationCode = '' ||
location_table.locationCode || '')';
END LOOP;
END;
--
Jason Aleski / IT Specialist
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find inconsistencies in data with date range
@ 2015-03-06 22:12 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Jason Aleski <jason.aleski@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2015-03-06 22:12 UTC (permalink / raw)
To: Jason Aleski <jason.aleski@gmail.com>; pgsql-sql
On 03/06/2015 01:38 PM, Jason Aleski wrote:
> I know I can do this Java, but I'd rather have this running as a Stored
> Procedure. What I am wanting to do is identify and potentially correct
> the summary data for date inconsistencies. We have policies/red flag
> reports in place to keep this from happening, but we are now cleaning up
> history. The query below works on a per store basis, but I'd like to be
> able to run this for all stores in the location table.
>
> I've looked at some procedure codes regarding looping, but everything I
> try to create seems to give me problems. THe code I'm trying is also
> below. Does anyone have any suggestions on how to accomplish this?
>
>
>
> _Working Tables_
> locations - table contains store information, startup date, address, etc
> daily_salessummary - table holds daily sales summary by store
> (summary should be updated nightly). eod_ts is End of Day Timestamp.
>
> _Query_
> WITH datelist AS(
> SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek
> FROM (SELECT date as GenDate
> FROM
> generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
> day'::interval) date
> ) AS t1
> )
> SELECT gendate FROM datelist AS t1
> WHERE gendate NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1
> JOIN locations AS t2 ON t1.location_id = t2.row_id
> WHERE t2.locationCode = 'US_FL_TAMPA_141')
>
> AND gendate > (SELECT start_date FROM locations WHERE locationCode =
> 'US_FL_TAMPA_141')
First in above and in variation below I would probably do some alias
renaming. I pretty sure t1 means different things throughout the query,
but is hard to follow exactly what.
>
>
> _Desired Output_ - could output to an exceptions table
> StoreCode 'US_FL_TAMA_141' missing daily summary for 1998-01-01
> StoreCode 'MX_OAXACA_SALINA_8344' missing daily summary for 2011-06-05
>
>
> _ProcedureSQL_ (contains unknown errors)
> DECLARE
> CURSOR location_table IS
> SELECT locationCode FROM locations;
> BEGIN
> FOR thisSymbol IN ticker_tables LOOP
> EXECUTE IMMEDIATE 'WITH datelist AS(
> SELECT t1.GenDate as
> gendate, extract(dow from GenDate) AS dayofweek
> FROM (SELECT date as
> GenDate
> FROM
> generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
> day'::interval) date
> ) AS t1
> )
> SELECT gendate FROM
> datelist AS t1
> WHERE gendate NOT IN
> (SELECT t1.eod_ts FROM daily_salessummary AS t1
> JOIN locations AS t2 ON t1.location_id = t2.row_id
> WHERE t2.locationCode = '' || location_table.locationCode || '')
> AND gendate > (SELECT
> start_date FROM locations WHERE locationCode = '' ||
> location_table.locationCode || '')';
> END LOOP;
> END;
I do not use cursors enough in plpgsql to be sure, but I think the above
definition is incorrect:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html
To reduce the moving parts I would write the function without the cursor
and just hardwire the location information to start with to get a
working sample.
>
>
>
> --
> Jason Aleski / IT Specialist
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find inconsistencies in data with date range
@ 2015-03-07 03:00 David G Johnston <david.g.johnston@gmail.com>
parent: Jason Aleski <jason.aleski@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: David G Johnston @ 2015-03-07 03:00 UTC (permalink / raw)
To: pgsql-sql
Jason Aleksi wrote
> I've looked at some procedure codes regarding looping, but everything I
> try to create seems to give me problems. THe code I'm trying is also
> below. Does anyone have any suggestions on how to accomplish this?
I would build a master table of stores and dates and then write a query to
update a third field from null to the number of records found for the given
combination. When all the nulls are gone you can scan for zeros to figure
out what combinations are missing data. If you have a matching index the
queries should execute reasonably efficiently and you either call it from a
function in the database or externally on one or more threads depending on
where you expect to encounter the processing bottleneck. You can process
more than one day or store at a time if so desired but there will likely be
a point of diminishing returns depending on the volume of data. I would
probably do all days for one store in a given year at a time.
David J.
--
View this message in context: http://postgresql.nabble.com/Find-inconsistencies-in-data-with-date-range-tp5840865p5840891.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find inconsistencies in data with date range
@ 2015-03-07 16:42 s d <daku.sandor@gmail.com>
parent: Jason Aleski <jason.aleski@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: s d @ 2015-03-07 16:42 UTC (permalink / raw)
To: Jason Aleski <jason.aleski@gmail.com>; +Cc: pgsql-sql
Hi,
Something like this?
It inserts error records into a table called locationrep.
create or replace function finderror() returns void as $$
declare
startd date;
daterec record;
begin
select into startd min(startdate) from location; --identify the earliest
opening date
--iterating trough dates from that date until now
for daterec in select generate_series::date as repdate from
generate_series(startd,now()::date,'1 day'::interval) loop
--insert ito the error table all the shops which not have an
entry from the current date and opened before the said date
insert into locationrep select shop,daterec.repdate from location where
startdate<=daterec.repdate and not exists(select 1 from daily_salessummary
where shop=location.shop and reportdate=daterec.repdate);
end loop;
end;
$$ language plpgsql;
Regards,
Sándor Daku
On 6 March 2015 at 22:38, Jason Aleski <jason.aleski@gmail.com> wrote:
> I know I can do this Java, but I'd rather have this running as a Stored
> Procedure. What I am wanting to do is identify and potentially correct the
> summary data for date inconsistencies. We have policies/red flag reports
> in place to keep this from happening, but we are now cleaning up history.
> The query below works on a per store basis, but I'd like to be able to run
> this for all stores in the location table.
>
> I've looked at some procedure codes regarding looping, but everything I
> try to create seems to give me problems. THe code I'm trying is also
> below. Does anyone have any suggestions on how to accomplish this?
>
>
>
> *Working Tables*
> locations - table contains store information, startup date, address, etc
> daily_salessummary - table holds daily sales summary by store (summary
> should be updated nightly). eod_ts is End of Day Timestamp.
>
> *Query*
> WITH datelist AS(
> SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek
> FROM (SELECT date as GenDate
> FROM generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
> day'::interval) date
> ) AS t1
> )
> SELECT gendate FROM datelist AS t1
> WHERE gendate NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1
> JOIN locations AS t2 ON t1.location_id = t2.row_id
> WHERE t2.locationCode = 'US_FL_TAMPA_141')
>
> AND gendate > (SELECT start_date FROM locations WHERE locationCode =
> 'US_FL_TAMPA_141')
>
>
> *Desired Output* - could output to an exceptions table
> StoreCode 'US_FL_TAMA_141' missing daily summary for 1998-01-01
> StoreCode 'MX_OAXACA_SALINA_8344' missing daily summary for 2011-06-05
>
>
> *ProcedureSQL* (contains unknown errors)
> DECLARE
> CURSOR location_table IS
> SELECT locationCode FROM locations;
> BEGIN
> FOR thisSymbol IN ticker_tables LOOP
> EXECUTE IMMEDIATE 'WITH datelist AS(
> SELECT t1.GenDate as
> gendate, extract(dow from GenDate) AS dayofweek
> FROM (SELECT date as
> GenDate
> FROM
> generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
> day'::interval) date
> ) AS t1
> )
> SELECT gendate FROM datelist
> AS t1
> WHERE gendate NOT IN (SELECT
> t1.eod_ts FROM daily_salessummary AS t1
> JOIN
> locations AS t2 ON t1.location_id = t2.row_id
> WHERE
> t2.locationCode = '' || location_table.locationCode || '')
>
> AND gendate > (SELECT
> start_date FROM locations WHERE locationCode = '' ||
> location_table.locationCode || '')';
> END LOOP;
> END;
>
>
>
> --
> Jason Aleski / IT Specialist
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find inconsistencies in data with date range
@ 2015-03-12 04:10 Jason Aleski <jason.aleski@gmail.com>
parent: s d <daku.sandor@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jason Aleski @ 2015-03-12 04:10 UTC (permalink / raw)
To: pgsql-sql
In case anyone else needs similar code, I was able to get this working.
Below is the code that pulls the missing dates using a cursor and
returns the information into a table. I'm sure there may be a way to
make the code more efficient, but considering this will only get ran
maybe once a quarter (for quarterly reports), it works for me. With
700+ stores, it takes about 30 minutes to fully run from a reporting
server. I have a JAVA program that queries the function "SELECT * FROM
eod_missing_dates();" Then sends all the missing dates to a RabbitMQ
server to with a worker program to try to rebuild the missing eod
summaries and if not, it will send a message to the store managers.
Hopefully this code will help someone else!
CREATE OR REPLACE FUNCTION eod_missing_dates()
RETURNS TABLE(store_id uuid, location character varying, missing_ts
timestamp with time zone) AS
$BODY$
DECLARE
location_cursor CURSOR FOR SELECT * FROM locations ORDER BY store_id;
store_rec location%ROWTYPE;
BEGIN
CREATE TEMP TABLE dr_temptable(dr_ts, dr_dow) AS (SELECT t1.GenDate
as gendate, extract(dow from GenDate) AS dayofweek
FROM (SELECT date as GenDate
FROM
generate_series('1950-01-01'::date,CURRENT_TIMESTAMP::date,'1
day'::interval) date
) AS t1
WHERE extract(dow from GenDate) NOT IN (0,6));
OPEN location_cursor;
LOOP
FETCH location_cursor INTO store_rec;
EXIT WHEN store_rec IS NULL;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO '%', 'Checking data/dates for ' || store_rec.location;
RETURN QUERY SELECT store_rec.row_id as store_id,
store_rec.location AS location, dr_ts AS missing_ts FROM dr_temptable AS t1
WHERE dr_ts NOT IN (SELECT t1.eod_ts FROM
daily_salessummary AS t1
JOIN location AS t2 ON t1.store_id = t2.row_id
WHERE t2.location = store_rec.location)
AND dr_ts > (SELECT start_date FROM locations
WHERE location=store_rec.location);
END LOOP;
CLOSE location_cursor;
DROP TABLE dr_temptable;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
Jason Aleski / IT Specialist
>
> On 6 March 2015 at 22:38, Jason Aleski <jason.aleski@gmail.com
> <mailto:jason.aleski@gmail.com>> wrote:
>
> I know I can do this Java, but I'd rather have this running as a
> Stored Procedure. What I am wanting to do is identify and
> potentially correct the summary data for date inconsistencies. We
> have policies/red flag reports in place to keep this from
> happening, but we are now cleaning up history. The query below
> works on a per store basis, but I'd like to be able to run this
> for all stores in the location table.
>
> I've looked at some procedure codes regarding looping, but
> everything I try to create seems to give me problems. THe code
> I'm trying is also below. Does anyone have any suggestions on how
> to accomplish this?
>
>
>
> _Working Tables_
> locations - table contains store information, startup date,
> address, etc
> daily_salessummary - table holds daily sales summary by store
> (summary should be updated nightly). eod_ts is End of Day Timestamp.
>
> _Query_
> WITH datelist AS(
> SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek
> FROM (SELECT date as GenDate
> FROM
> generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
> day'::interval) date
> ) AS t1
> )
> SELECT gendate FROM datelist AS t1
> WHERE gendate NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1
> JOIN locations AS t2 ON t1.location_id =
> t2.row_id
> WHERE t2.locationCode = 'US_FL_TAMPA_141')
>
> AND gendate > (SELECT start_date FROM locations WHERE
> locationCode = 'US_FL_TAMPA_141')
>
>
> _Desired Output_ - could output to an exceptions table
> StoreCode 'US_FL_TAMA_141' missing daily summary for 1998-01-01
> StoreCode 'MX_OAXACA_SALINA_8344' missing daily summary for 2011-06-05
>
>
> _ProcedureSQL_ (contains unknown errors)
> DECLARE
> CURSOR location_table IS
> SELECT locationCode FROM locations;
> BEGIN
> FOR thisSymbol IN ticker_tables LOOP
> EXECUTE IMMEDIATE 'WITH datelist AS(
> SELECT t1.GenDate as
> gendate, extract(dow from GenDate) AS dayofweek
> FROM (SELECT date
> as GenDate
> FROM
> generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
> day'::interval) date
> ) AS t1
> )
> SELECT gendate FROM
> datelist AS t1
> WHERE gendate NOT IN
> (SELECT t1.eod_ts FROM daily_salessummary AS t1
> JOIN locations AS t2 ON t1.location_id =
> t2.row_id
> WHERE t2.locationCode = '' ||
> location_table.locationCode || '')
> AND gendate > (SELECT
> start_date FROM locations WHERE locationCode = '' ||
> location_table.locationCode || '')';
> END LOOP;
> END;
>
>
>
> --
> Jason Aleski / IT Specialist
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-03-12 04:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-03-06 21:38 Find inconsistencies in data with date range Jason Aleski <jason.aleski@gmail.com>
2015-03-06 22:12 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-03-07 03:00 ` David G Johnston <david.g.johnston@gmail.com>
2015-03-07 16:42 ` s d <daku.sandor@gmail.com>
2015-03-12 04:10 ` Jason Aleski <jason.aleski@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox