SQL: decide to use or not

Question

Hallo: I did some text processing of the database using Shell and Python. For interaction, I am going to do this using SQL. SQL is suitable for some query tasks. But I'm not sure if SQL can handle all my tasks. Consider an 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 ​​for a specific time interval #time. Suppose that 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 this from a SQL tutorial than doing this with a shell and python.
So my friends, any suggestion?

Thanks!

Answer 1

You can do this in mysql with a fairly simple query:

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

The query uses the fact that integer division by 100 will give you the groups of intervals that you described (for example, 111 DIV 100 = 1 and 134 DIV 100 = 1 )

Answer 2

The question is not clear to me.

• There is a database and you want to process the data there, and you ask to use or not to use SQL? Answer: Yes, SQL is the interface for many databases, it is quite standard for the main databases with minor changes. Use it.

• If you cannot use or not use a database to store and process certain values, it is important to use the data type, amount of data, and relationships in the data. If you want to process a large amount of data and there is a connection between data sets, then you can use relational database systems such as MySql. The problem you described is a very simple problem for RMDBS. Let me give an example:

select the amount (value) from the elements where time> = 100 and time <= 200

But if the data set is small, you can easily process it using file I / O.

• If you use Python, you can use Sqlite as a database, it is a very light, simple, easy to use and widely used database. You can also use SQL with Sqlite.

If you can provide more details, we can help more.

Answer 3

Yes, a SQL-based database like MySQL is likely to be a great choice for your project. You can also look at SQLite if you do not want to configure the server.

A good introduction to SQL will be helpful to you. I suggest SQL for dummies of Allen Taylor.