Basic SQLite question

[jchd]

Really?

I sincerely doubt you have tabulated (or are going to tabulate) 200¹⁰ values, even if in the form of a binary outcome.

200^{10 =} 102 400 000 000 000 000 000 000

Storing binary values, that would be a bare minimum of 102.4 / 8 10²¹ = 12.8 zettabytes

So SQLite might still be an efficient way to solve "someting" in your area of interest but the problem has to be clearly exposed.

EDIT: to answer your recent post on the list, yes reply to the group (sqlite-users@sqlite.org) but please keep track of the thread (depends on your mail client).

Edited April 23, 2013 by jchd

[cag8f]

Definitely not 200¹⁰. ~10 criteria (I don't know the exact number yet), with anywhere from 8 to 150 possibilities per column. I estimated about 400k values, which I am resigned to entering manually (many are redundant).

>> SQLite might still be an efficient way to solve "someting" in your area of interest

'Efficient' with respect to what?

>> but the problem has to be clearly exposed.

Meaning what exactly? I'm under the impression the problem is clearly exposed. It is completely analogous to the height/weight problem, only scaled up. What else do you think I'm missing?

[jchd]

Just that with your low estimate (8 possibilities per column) you end up with 8¹⁰ = 1 073 741 824 entries, not 400k.

With an average estimate (8 + 150) / 2 = 79 possibilities per column you end up with 79¹⁰ entries (= 9 468 276 082 626 847 201), not 400k.

[cag8f]

The numbers were ceiling guestimates I inserted merely to illustrate the potential scale of my scenario. As it stands, I have 5 different criteria, with possibilities of 8, 9, 3, 12, and 170. giving a total of ~440k entries.

What about this question I posed earlier? This is the answer I've been driving towards since my original post:

>> SQLite might still be an efficient way to solve "someting" in your area of interest

'Efficient' with respect to what?

[jchd]

OK things are getting sharper but get ready for a painful time loading data!

Efficient in query speed and disk and/or RAM (your choice). Don't get into premature optimization pitfall!

If you're ready with your project, making a single table with columns holding min and max for each criterion and group column is OK.

Then your queries can be as simple as:

select thegroup from mychart where $height between minheight and maxheight and $weight between minweight and maxweight and ...

Making that fast implies creating indices on criterion data.

The whole baby can certainly fit in memory.

To answer the parallel question, yes it will be faster than Excel, once tuned if necessary.

Edited April 23, 2013 by jchd

[cag8f]

>> get ready for a painful time loading data!

Yes I know. There may be a way to automate some of it.

>> Don't get into premature optimization pitfall!

I'm way too novice to understand this right now.

>> Making that fast implies creating indices on criterion data.

I don't quite understand this either, but I *am* interested in making it fast. I gather I should look up indices for more information?

[jchd]

No leave that for the last step!

[cag8f]

OK so indices are not something I need to implement from step 1. i.e. I can assemble my database, then implement them after.

[jchd]

Correct. I advise you to download the free version of SQLite Expert and start playing with it by building toy examples in order to familiarize yourself with SQL. Also have a look at some of the links in my signature.

[cag8f]

I've already looked at all your links, but didn't quite follow the tutorials or book. There are plenty of others though online that I've been trying. I hadn't heard of SQLite Expert, but I'll download it and start playing with it. Thanks for your advice and patience!

[kylomas]

cag8f,

Can you show an example of what your data tables might look like (criteria)?

And explain, step by step, how you decide to categorize (groups) based on these tables?

kylomas

[BrewManNH]

cag8f,

Can you show an example of what your data tables might look like (criteria)?

And explain, step by step, how you decide to categorize (groups) based on these tables?

kylomas

Thats what I've been asking him for 3 days now, don't expect an answer any time soon that makes any kind of sense.

[kylomas]

@BrewmanNH - Yes, despite very specific answers from you and jchd...I thought that phrased this way might show the OP that tool selection is premature, define the problem first. (although I'm sure that SQLite is the answer given that spreadsheets are being considered)

kylomas

[cag8f]

>> tool selection is premature, define the problem first.

OK this statement is helpful. I'm learning here.

>> And explain, step by step, how you decide to categorize (groups) based on these tables?

There is no algorithm (or set of algorithms) that determines every group assignment. I know that Height2/Weight1/Age3 corresponds to Group A based on prior experience. Is that beginning to answer your question?

.

Below is a simplified version of what my data table might look like. For illustrative purposes, this table contains only 3 criteria, with 3 possible entries for each. The user would enter a height, weight, and age, and the program would access the data file, armed with the user entered data, and output the corresponding group. Again, assume this table will be larger in scale. This table has 27 different combinations, while my actual table will have ~400k different combinations (5 criteria, with 8, 9, 3, 12, and 170 possible entries respectively).

edit: fail on posting the data table. Re-posting the table. This is the best I can do with my limited formatting skills.

Ht Wt Age Group

H1 W1 A1 B

H1 W1 A2 B

H1 W1 A3 A

H1 W2 A1 B

H1 W2 A2 A

H1 W2 A3 A

H1 W3 A1 B

H1 W3 A2 B

H1 W3 A3 A

H2 W1 A1 B

H2 W1 A2 A

H2 W1 A3 A

H2 W2 A1 B

H2 W2 A2 B

H2 W2 A3 A

H2 W3 A1 B

H2 W3 A2 A

H2 W3 A3 B

H3 W1 A1 A

H3 W1 A2 A

H3 W1 A3 A

H3 W2 A1 B

H3 W2 A2 B

H3 W2 A3 A

H3 W3 A1 B

H3 W3 A2 B

H3 W3 A3 A

Edited April 24, 2013 by cag8f

[kylomas]

cag8f,

The assignment to groups has to follow some method whether it be an algorithm or some rule of thumb that you go by. It is certainly not arbitrary. Given that the number of groups is less than the number of criteria then group selection can be expressed in ranges. Would you say that is true?

kylomas

[cag8f]

>> The assignment to groups has to follow some method whether it be an algorithm or some rule of thumb that you go by

The assignment to groups does indeed follow rules that I know. These are empirical relationships. I *know* that a combination of h1/w3/a2 corresponds to Group B, I *know* that a combination of h3/w3/a1 corresponds to Group B, and so forth. I've literally observed these correspondences previously with with my own two eyes.

>> Given that the number of groups is less than the number of criteria then group selection can be expressed in ranges. Would you say that is true?

In my actual scenario, the number of groups will be greater than the number of criteria.

[BrewManNH]

Given that the number of groups is less than the number of criteria then group selection can be expressed in ranges.

That was my thinking too, there's no need for look up tables, or a database if we could figure out what puts person A into group A and person B into group B, which unfortunately, we have never gotten an answer to that question.

Now we're getting that there are more than 2 groups, and more criteria than we're being shown. I give up, and anyone else that wants to wade through this morass is more than welcome.

[cag8f]

>> Now we're getting that there are more than 2 groups, and more criteria than we're being shown.

I've said since my 2nd post that that there were more than 2 groups and 2 criteria, and that I was only presenting a simplified model of my data

>> if we could figure out what puts person A into group A and person B into group B,

These are empirical relationships. Right now there is no set of rules that determines what puts person A into group A, and person B into group B. Does that make sense to you?

[Clark]

As Brewman is indicating, this can be done by simple inference rules.

SUch as

if A3 and NOT (H2 and W3) THEN Group = A.

This one line eliminates 10 lines in your table.

Once the rules are known then it becomes simple.

[cag8f]

Sure that's reasonable simple in this case, and you created that rule by eye. But my actual data set contains 5 criteria and up to 200 options for each criterion.