javascript - Creating a continuous data set from irregular, dated rows -

javascript - Creating a continuous data set from irregular, dated rows -

i have table:

table "public.transaction" column | type | modifiers ------------+-----------------------------+---------------------------------------------------------- id | integer | not null default nextval('transaction_id_seq'::regclass) account_id | integer | note | character varying | date | timestamp without time zone | amount | numeric |

it contains transactions in format:

id | account_id | note | date | amount ----+------------+----------------------------------+---------------------+-------- 1 | 1 | loopia ab | 2013-02-07 00:00:00 | -178 2 | 1 | Åsgatan 2 kÖk & | 2013-02-07 00:00:00 | -226 3 | 1 | burger king oden | 2013-02-06 00:00:00 | -89 4 | 1 | olearys 917 | 2013-02-06 00:00:00 | -309 5 | 1 | taxi stockholm | 2013-02-06 00:00:00 | -875 6 | 1 | gret indian rest | 2013-02-06 00:00:00 | -85 8 | 1 | video rullen | 2013-02-04 00:00:00 | -169 9 | 1 | ica supermarket | 2013-02-04 00:00:00 | -196 10 | 1 | ica supermarket | 2013-02-03 00:00:00 | -110

i feed info d3 in next format:

[ { "note": "texas longhorn", "date": "2013-01-10t00:00:00", "amount": 110, "id": 74, "account_id": 1 }, { "note": "google *feo medi", "date": "2013-01-10t00:00:00", "amount": 22, "id": 73, "account_id": 1 }, { "note": "pressbyran 5122", "date": "2013-01-10t00:00:00", "amount": 13, "id": 77, "account_id": 1 }, { "note": "ica supermarket", "date": "2013-01-10t00:00:00", "amount": 106, "id": 76, "account_id": 1 }, { "note": "hÅr 3000", "date": "2013-01-10t00:00:00", "amount": 345, "id": 75, "account_id": 1 }, { "note": "pressbyran 5122", "date": "2013-01-11t00:00:00", "amount": 19, "id": 72, "account_id": 1 }, { "note": "birka punkten", "date": "2013-01-11t00:00:00", "amount": 79, "id": 71, "account_id": 1 } ]

d3 streamgraphs requires info points present. have set all dates, without transactions, in info feed d3.

i love input on how create effiently of tools available. can play around live illustration @ http://bl.ocks.org/joar/4747134/a702cf79bf10b1438cc665a2438b3f5cf9ab8bf0

you want generate_series set of dates covering target region, left outer bring together transaction table against it. see this sqlfiddle example.

select x.gendate, t.account_id, t.id, t.note, t.amount generate_series( (select min("date") transaction), (select max("date") transaction), interval '1' day ) x(gendate) left outer bring together transaction t on (t."date" = x.gendate) order x.gendate;

you can generate desired info format postgresql's json functions, per this sqlfiddle.

with continuous_tx ( select x.gendate "date", t.account_id, t.id, t.note, t.amount generate_series( (select min("date") transaction), (select max("date") transaction), interval '1' day ) x(gendate) left outer bring together transaction t on (t."date" = x.gendate) order x.gendate ) select array_to_json(array_agg(continuous_tx ),'t') continuous_tx;

... though haven't tested feeding graphing tool.

javascript python sqlalchemy d3.js postgresql-9.1