can pivot be applied to group by ?

>> ( And yes I realize this is an example of an EAV (Entity-Attribute-Value) table which apparently is frowned upon <<

Frowned? More like "spit upon", regarded as Cancer, etc.

>> .. but what other choice do I have if I need to support a set of attributes only known at data-load-time?) <<

0) RDBMS is not meant for a "I have no idea what the hell is going on
and I am not going to figure it out first!" environment. A better
question is why don't you know? This sounds like web-surfing random
porno sites to see what kind of new virus you can get into your
system.

1) Free text. This is what we did in the old days with pens and field
books when researching a totally new subject area. The idea was to
come up with the scales, measurements, data models, etc. to build a
file system or DBMS for it.

2) XML. This assumes that you have a hierarchical problem and don't
need the performance and power of SQL.

You keep posting HOW you want to kludge something together, but you
never post WHAT you are actually trying to do. What is the actual
problem you are trying to model?
 >> Stay informed about: can pivot be applied to group by ? 

Thank you so much for expanding my consciousness. I was not aware that I was
"kludging", rather I was under the apparently severe misimpression that I
was researching and designing.

I'm designing a system for working with per-period financial data where the
set of attribute values is unknown until load time of various data-sets.

For example, it might be bar data ( open, high, low, close, volume ).

Or it might be derivatives data ( equity price, near-strike near-month call
option price, near-strike near-month put option price, implied volatility,
etc.)

Ad-hoc design/add of additional tables for the particulars of a each set of
new data to be loaded is not an option.



"--CELKO--" wrote in message

>>> ( And yes I realize this is an example of an EAV
>>> (Entity-Attribute-Value) table which apparently is frowned upon <<
>
> Frowned? More like "spit upon", regarded as Cancer, etc.
>
>>> .. but what other choice do I have if I need to support a set of
>>> attributes only known at data-load-time?) <<
>
> 0) RDBMS is not meant for a "I have no idea what the hell is going on
> and I am not going to figure it out first!" environment. A better
> question is why don't you know? This sounds like web-surfing random
> porno sites to see what kind of new virus you can get into your
> system.
>
> 1) Free text. This is what we did in the old days with pens and field
> books when researching a totally new subject area. The idea was to
> come up with the scales, measurements, data models, etc. to build a
> file system or DBMS for it.
>
> 2) XML. This assumes that you have a hierarchical problem and don't
> need the performance and power of SQL.
>
> You keep posting HOW you want to kludge something together, but you
> never post WHAT you are actually trying to do. What is the actual
> problem you are trying to model?
>
 >> Stay informed about: can pivot be applied to group by ? 

>> I'm designing a system for working with per-period financial data where the set of attribute values is unknown until load time of various data-sets. <<

You ought to know the attributes from each source and something about
the scales and units of measurement used.

>> For example, it might be bar data ( open, high, low, close, volume ). <<

That is one kind of table and you seem to have a start on the column
names for it ( I was looking for an ISIN, CUSIP or something for a
key).

>> Or it might be derivatives data .. <<

That is a second kind of table and you seem to have a start on the
column names for it, too.

I hope that it doesn't bother you that you don't put your squids and
your automobiles in the same table. But for your database, are these
really different creatures? That is, do you want to put differently
formatted data of the same kind into one table? Statisticians have
had this problem for centuries and have tools for it. There are some
simple things like converting time to UTC, measurements to common
scales (pounds to kilograms, gallons to liters, etc.), or time based
conversions (dollars to yen on a particular day). Then there is the
complex stuff to adjust for missing or erroneous data.

What you want is a data warehouse and some good data mining tools.
And with 100's of millions of data points, you want a RDBMS designed
for this kind of work.

>> Ad-hoc design/add of additional tables for the particulars of a each set of
new data to be loaded is not an option. <<

Option? It is a necessity for the staging tables, so you can scrub
and load it. How many different data sources do you have; my guess
is less than 100 -- there are not that many providers for financial
data.
 >> Stay informed about: can pivot be applied to group by ? 

It's not a question of standard financial data sources per se , it's a
question of end-users providing custom data in custom formatted files.

I have abandoned the idea of multiple datatypes. The only data I will handle
is decimal(18,5).

I may very well be proved to be wrong, but it seems possible to design a
performant system handling series data consisting of an arbitray number of
attributes all of identical datatype.

Perhaps 100 million row rowsets will not be performant but million row
rowsets should be.

If I use temp tables and/or joined derived tables to "pivot out my attribute
set", approximately how much less performant is this compared with a flat
table design ? e.g.

Id bigint identity(1,1)
BarNumber bigint
DataDateTime DateTime not null
Open decimal(18,5)
High decimal(18,5)
Low decimal(18,5)
Close decimal(18,5)
Volume decimal(18,5)





"--CELKO--" wrote in message

>>> I'm designing a system for working with per-period financial data where
>>> the set of attribute values is unknown until load time of various
>>> data-sets. <<
>
> You ought to know the attributes from each source and something about
> the scales and units of measurement used.
>
>>> For example, it might be bar data ( open, high, low, close, volume ).
>>> <<
>
> That is one kind of table and you seem to have a start on the column
> names for it ( I was looking for an ISIN, CUSIP or something for a
> key).
>
>>> Or it might be derivatives data .. <<
>
> That is a second kind of table and you seem to have a start on the
> column names for it, too.
>
> I hope that it doesn't bother you that you don't put your squids and
> your automobiles in the same table. But for your database, are these
> really different creatures? That is, do you want to put differently
> formatted data of the same kind into one table? Statisticians have
> had this problem for centuries and have tools for it. There are some
> simple things like converting time to UTC, measurements to common
> scales (pounds to kilograms, gallons to liters, etc.), or time based
> conversions (dollars to yen on a particular day). Then there is the
> complex stuff to adjust for missing or erroneous data.
>
> What you want is a data warehouse and some good data mining tools.
> And with 100's of millions of data points, you want a RDBMS designed
> for this kind of work.
>
>>> Ad-hoc design/add of additional tables for the particulars of a each set
>>> of
> new data to be loaded is not an option. <<
>
> Option? It is a necessity for the staging tables, so you can scrub
> and load it. How many different data sources do you have; my guess
> is less than 100 -- there are not that many providers for financial
> data.
>
>
 >> Stay informed about: can pivot be applied to group by ? 

Yes, you can group and pivot at the same time:

CREATE TABLE Attributes (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
attribute NVARCHAR(50) NOT NULL,
periodnumber BIGINT NOT NULL,
datadatetime DATETIME NOT NULL,
value DECIMAL(18,5) NOT NULL);

INSERT INTO Attributes VALUES ('Attr1', 1, '20080701', 1.45);
INSERT INTO Attributes VALUES ('Attr2', 1, '20080701', 1.75);
INSERT INTO Attributes VALUES ('Attr3', 1, '20080701', 1.20);
INSERT INTO Attributes VALUES ('Attr4', 1, '20080701', 1.59);
INSERT INTO Attributes VALUES ('Attr1', 2, '20080702', 1.59);
INSERT INTO Attributes VALUES ('Attr2', 2, '20080702', 1.80);
INSERT INTO Attributes VALUES ('Attr3', 2, '20080702', 1.30);
INSERT INTO Attributes VALUES ('Attr4', 2, '20080702', 1.80);

SELECT periodnumber,
datadatetime,
MAX([Attr1]) AS Attr1,
MAX([Attr2]) AS Attr2,
MAX([Attr3]) AS Attr3,
MAX([Attr4]) AS Attr4
FROM Attributes
PIVOT
(MAX(value) FOR attribute IN
([Attr1], [Attr2], [Attr3], [Attr4])) AS P
GROUP BY periodnumber, datadatetime;

HTH,

Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: can pivot be applied to group by ? 

>> It's not a question of standard financial data sources per se , it's a question of end-users providing custom data in custom formatted files. <<

Have you looked at ETL tools? That would load your warehouse and
scrub the data fairly fast.

>> The only data I will handle is decimal(18,5). <<

Sound right for currency -- enough decimal places for calculations and
so big that you can hold amounts of Yen.

>> I may very well be proved to be wrong, but it seems possible to design a performant system handling series data consisting of an arbitrary number of attributes all of identical data type. <<

But how do you know that all of the attributes are of the same data
type? You cannot reasonably model currency amounts with temporal
data; look at the number of idiots on these newsgroups who keep
temporal data in strings, etc.

Picking the right data type is the first part of the data integrity
constraints. Screw it up and life is hell for the rest of the lifetime
of the system.

>> If I use temp tables and/or joined derived tables to "pivot out my attribute set", approximately how much less performant is this compared with a flat table design ? e.g. <<

Why not use a real as hoc spreadsheet instead of faking it in SQL?
 >> Stay informed about: can pivot be applied to group by ?