Re: [sqlite] Degrouping, desummarizing or integrating headings
T&B wrote:
It comes as a CSV or tabbed text file, then into a table in my
database. So, it's in a table in my database, eg:
CREATE TABLE Shopping_Grouped(Aisle TEXT, Product TEXT, Cost REAL)
But the Aisle entries are in records by themselves, and apply to the
subsequent records containing Product and Cost, for example:
INSERT INTO Shopping_Grouped(Aisle) VALUES('Dairy');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Milk', 2);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cream', 1);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cheese', 3);
INSERT INTO Shopping_Grouped(Aisle) VALUES('Bakery');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 4);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 3);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cake', 2);
But I want to get it into this schema:
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Milk', 2);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cream', 1);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cheese', 3);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced', 4);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced', 3);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Cake', 2);
The heart of your problem is that the original database has no explicit
relation between the rows that have a value in the Aisle column and the
rows that don't but should. In general, since a table is nothing more
than a set (an unordered collection) of rows this information must be
supplied by some other means (i.e. user input or another table etc).
If you always insert the rows in the order shown above you can make use
of the fact that sqlite will assign rowids in the order the rows are
created. This is using a implementation detail of sqlite to supply the
missing information. There are a couple of ways to do this that come to
mind.
create table shopping as
select
(select B.Aisle from shopping_grouped as B where B.rowid =
(select max(C.rowid) from shopping_grouped as C
where C.Aisle is not null and C.rowid < A.rowid)) as Ailse,
A.Product as Product,
A.Cost as Cost
from shopping_grouped as A
where A.product is not null;
create table shopping2 as
select
(select B.Aisle from shopping_grouped as B
where B.rowid < A.rowid and B.Aisle is not null
order by B.rowid desc
limit 1) as Aisle,
A.Product as Product,
A.Cost as Cost
from shopping_grouped as A
where A.product is not null;
This gives this result in sqlite 3.3.13
sqlite> select * from shopping;
Ailse Product Cost
-- -- --
Dairy Milk2.0
Dairy Cream 1.0
Dairy Cheese 3.0
Bakery Sliced 4.0
Bakery Sliced 3.0
Bakery Cake2.0
sqlite> select * from shopping2;
Aisle Product Cost
-- -- --
Dairy Milk2.0
Dairy Cream 1.0
Dairy Cheese 3.0
Bakery Sliced 4.0
Bakery Sliced 3.0
Bakery Cake2.0
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Degrouping, desummarizing or integrating headings
Thanks Puneet for your response.
this is a programming problem and not necessarily a SQLite problem.
Well, I can program it, but hope that instead there's a way to do it
within SQL.
Nevertheless, you don't specify how you are getting this summary
data... are they in a database? are they just a text file? Are they
XML?
It comes as a CSV or tabbed text file, then into a table in my
database. So, it's in a table in my database, eg:
CREATE TABLE Shopping_Grouped(Aisle TEXT, Product TEXT, Cost REAL)
But the Aisle entries are in records by themselves, and apply to the
subsequent records containing Product and Cost, for example:
INSERT INTO Shopping_Grouped(Aisle) VALUES('Dairy');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Milk', 2);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cream', 1);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cheese', 3);
INSERT INTO Shopping_Grouped(Aisle) VALUES('Bakery');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 4);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 3);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cake', 2);
But I want to get it into this schema:
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Milk', 2);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cream', 1);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cheese', 3);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced',
4);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced',
3);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Cake', 2);
You could create a schema out of this normalizing aisles and products
CREATE TABLE aisles (aisle_id, aisle_name);
CREATE TABLE products (product_id, product_name, product_cost,
aisle_id);
It's the "normalizing" that I'm asking how to do, via SQL (ie clever
SELECT statements).
and go from there with
SELECT a.aisle_name, p.product_name, p.product_cost
FROM products p JOIN aisles a ON p.aisle_id = a.aisle_id
That's the reverse of what I need. The data is already in this final
form and I need to "unscramble" it.
you will have to figure out how to get your source data into the
SQLite schema you create
It's already in the schema I outlined above. But how to get it into
the schema I want, is my question.
but that should be trivial depending on your programming environment.
But is it possible via SQL?
Thanks,
Tom
From: T&B <[EMAIL PROTECTED]>
Date: 14 March 2007 3:07:24 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Degrouping, desummarizing or integrating headings
Hi All,
I come across a fair bit of source data in summary format, where the
one field's values are used as a heading for a group of records for
which it applies. For instance, this shopping list:
Aisle Product Cost
Dairy
Milk $2
Cream$1
Cheese $3
Bakery
Sliced $4
Rolls$3
Cake $2
How can I select the data from that table of 8 records so that the
result is this table of 6 records?:
Aisle Product Cost
Dairy Milk $2
Dairy Cream$1
Dairy Cheese $3
Bakery Sliced $4
Bakery Rolls$3
Bakery Cake $2
Thanks,
Tom
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Degrouping, desummarizing or integrating headings
On 3/13/07, T&B <[EMAIL PROTECTED]> wrote: Hi All, I come across a fair bit of source data in summary format, where the one field's values are used as a heading for a group of records for which it applies. For instance, this shopping list: Aisle Product Cost Dairy Milk $2 Cream$1 Cheese $3 Bakery Sliced $4 Rolls$3 Cake $2 How can I select the data from that table of 8 records so that the result is this table of 6 records?: Aisle Product Cost Dairy Milk $2 Dairy Cream$1 Dairy Cheese $3 Bakery Sliced $4 Bakery Rolls$3 Bakery Cake $2 this is a programming problem and not necessarily a SQLite problem. Nevertheless, you don't specify how you are getting this summary data... are they in a database? are they just a text file? Are they XML? You could create a schema out of this normalizing aisles and products CREATE TABLE aisles (aisle_id, aisle_name); CREATE TABLE products (product_id, product_name, product_cost, aisle_id); and go from there with SELECT a.aisle_name, p.product_name, p.product_cost FROM products p JOIN aisles a ON p.aisle_id = a.aisle_id you will have to figure out how to get your source data into the SQLite schema you create, but that should be trivial depending on your programming environment. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Degrouping, desummarizing or integrating headings
Hi All, I come across a fair bit of source data in summary format, where the one field's values are used as a heading for a group of records for which it applies. For instance, this shopping list: Aisle Product Cost Dairy Milk $2 Cream$1 Cheese $3 Bakery Sliced $4 Rolls$3 Cake $2 How can I select the data from that table of 8 records so that the result is this table of 6 records?: Aisle Product Cost Dairy Milk $2 Dairy Cream$1 Dairy Cheese $3 Bakery Sliced $4 Bakery Rolls$3 Bakery Cake $2 Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
