SQL: to decide to use it or not

Question

Hallo: I did some text processing of a database using Shell and Python. For interoperability, I am thinking to do it using SQL. SQL is good for some query task. But i am not sure if the SQL can handle all my tasks. Consider one example database:

item | time | value 
-----+------+-------
 1   | 134  |   3
 2   | 304  |   1
 3   | 366  |   2
 4   | 388  |   2
 5   | 799  |   6
 6   | 111  |   7

I need to profile the sum of #values over certain #time interval. Suppose the time interval is 100, the result should be:

time_interval | sumvalue
--------------+----------
      1       |   10      -- the time interval from 100 to 199
      3       |    5      -- the time interval from 300 to 399
      7       |    6      -- the time interval from 700 to 799

I could not find a better way to do it from the SQL text book than to do it using shell and python.
So my SO friends, any suggestion?

Thanks!

Answer 1

You should be able to do it in mysql with a pretty simple query:

SELECT time DIV 100, SUM(value) FROM yourtable
   GROUP BY time DIV 100

The query takes advantage of the fact that integer division by 100 will give you the interval groupings you have described (eg. 111 DIV 100 = 1 and 134 DIV 100 = 1)

Answer 2

Question is not clear to me.

• There is a database and you want to process data from there, and you are asking to use or not to use SQL? Answer:Yes, SQL is an interface to many databases, it is quite standart for major databases with minor changes. Use it.

• If you cannot decide to use or not to use a database for storing and processing some values, then data type, amount of data and relations in data is important. If you want to handle large amount of data and there is relation between datasets, then you may want to use relational database systems such as MySql. The problem you told is a very simple problem for RMDBS. Let me give an example:

select sum(value) from items where time>=100 and time<=200

But if dataset is small you can easily handle it with file I/O.

• If you will use Python, you may want to use Sqlite as database, it is very lightweight, simple, easy to use and widely used database. You can use SQL with Sqlite too.

If you can give clearer details, we can help more.