Android Question SqLite create_aggregate function

[rgarnett1955]

Hi All

The other day I wrote a query for sqLite to return the standard deviation of a column.

SELECT STD(colFred), AVG(colFred) FROM tblFredsTable ...

Imagine my disappointment when I found that sqLite doesn't provide standard deviation or variance aggregate functions. All of the other mainstream relational databases do!

So what to do? I had to read in the whole data set and do the variance using Welford's method. Not much fun.

I had a look at

https://www.b4x.com/android/forum/threads/sqlite-make-custom-functions-and-regex.87073/#content

but this only seems to apply to single rows.

I did some more research and found that sqLite provides a create_aggregate function.

My question is can I use this function in b4x to provide the stats functions I require?

I am assuming there is some java out there that provides the create_aggregate functionality.


Best Regards

Rob

 

[Erel]

Where is the documentation of this command?

 

[cimperia]

You can usually find a work-around. For example, for standard variation, this should work:

select 
      ((count(*) * sum(colValue * colValue) - (sum(colValue) * sum(colValue))/((count(*)-1) * (count(*)))) As std2
from 
     myTable

And from B4A code, take the square root of std2

 

[rgarnett1955]

Where is the documentation of this command?

Hi Erel,

Here is the sqLite website page for the sqlite3_create_function:

SqLite Doc. Create or Redefine Functions

They say:

These functions (collectively known as "function creation routines") are used to add SQL functions or aggregates or to redefine the behavior of existing SQL functions or aggregates. The only differences between the three "sqlite3_create_function*" routines are the text encoding expected for the second parameter (the name of the function being created) and the presence or absence of a destructor callback for the application data pointer. Function sqlite3_create_window_function() is similar, but allows the user to supply the extra callback functions needed by aggregate window functions.

Note there is no create_aggregate function per se in sqlite3 it turns out this was an sqLite 2.4.0++ function.

I will give Cimperia's query a try, but as I write lots of queries with multiple fields that require standard deviations grouped over time periods the suggested method could be a bit tedious to write and maintain.

One solution is to use SQL Server as the query engine, but this isn't without its pitfalls and inefficiencies not to mention that it adds another layer of Microsoft to the process.

I would like to use mySQL, but the installer won't work on my computer, although it works on my laptop.

Much better if Richard Hipp included standard deviation and variance in the sqlite3 engine as standard. I can't believe they haven't included these.

If adding sqLite aggregate function creation capability was possible in b4x it could be added to the jDBC server code at initialization and viola we have standard deviation and variance.

Best regards

Rob

 

[rgarnett1955]

You can usually find a work-around. For example, for standard variation, this should work:

select
      ((count(*) * sum(colValue * colValue) - (sum(colValue) * sum(colValue))/((count(*)-1) * (count(*)))) As std2
from
     myTable

And from B4A code, take the square root of std2

Hi cimperia,

Thanks for your suggestion. I will give it a try.

Best regards

Rob

 

[OliverA]

If adding sqLite aggregate function creation capability was possible in b4x it could be added to the jDBC server code at initialization and viola we have standard deviation and variance.

How would this apply to Android? Besides the JdbcSQL library, there is no JDBC support under Android.

 

[cimperia]

@rgarnett1955

It seems possible to use your own SQLite libraries (with the added functionality you require), thanks to this project SQLite Android Bindings. I must admit, I've never done it and I did read the doc. in full.

 

[rgarnett1955]

Hi

I don't know about Android. I am using the jRDC servers running on a Windows machine as a data server for both local and remote apps. I would like to include a variance / standard deviation function at the server end in B4J so I can calc aggregates including std deviation with only the output being transmitted to networked and local clients. Having to write a variance/std deviation function for sqLite is a bit crazy. It should be included as a standard function in sqLite. I am using sqLite because I am running it using an sd card as the storage medi in an ARM STM32H743 which has 1M of Ram and 2M of flash. It works very well. I also store the data in an exact copy of the embedded DB on the client PC in real-time over an ethernet (WiFi) link. I did it this way so there is no problem with data typing and it's easier to maintain.

To get over the problem on the server PC I either calculate the variance/std dev using Welford's method using Qt on half hour intervals of the data or I use MS SQL Server with the SQLite database linked to this server. MS SQL Server has a good range of standard aggregate functions including std deviation, but it's a bit big, complicated and expensive for general use.

I would much rather do everything in native sqLite as it's such a good fit for storing large amounts of homogeneous real-time data. It's also fast. One of my sqLite DB's is 6.81 Million records of 16 doubles and 26 bytes with an index on a double (the Julian Date/Time UTC). It can count the total number of these records in only 34 seconds. Of course if I retrieve only a small subset of this data by julian date/Time it generally responds in milli seconds (Hard disk seek time).

Using the Write Ahead Log (WAL) sqLite is also pretty robust against power loss, program termination etc.


Best regards
Rob

PS If anyone is thinking of running an sqLite DB on an embedded ARM processor and they need proven Virtual File System Drivers (VFS Layer) then go to the following link:

Power Monitor using sqLite 3 Amalgamation and SD Card

I know this is outside the scope of this forum, but I am using a headless B4J app to get the data from this system and using the jRDC Server and MS SQL Server to make the data available to other B4J and Qt UI Apps, with sqLite being the "central" db.

 

[rgarnett1955]

@rgarnett1955

It seems possible to use your own SQLite libraries (with the added functionality you require), thanks to this project SQLite Android Bindings. I must admit, I've never done it and I did read the doc. in full.

Hi

Thanks for that

I will give have a look at this and give it ago.

Best Regards
Rob

 

[RB Smissaert]

@rgarnett1955

It seems possible to use your own SQLite libraries (with the added functionality you require), thanks to this project SQLite Android Bindings. I must admit, I've never done it and I did read the doc. in full.

This is interesting as it would be nice to have
SQLite UDF's in B4A. Looks complicated though.

RBS

 

[cimperia]

Did I misread your post? I thought we were dealing with Android. Shouldn't your post be in the B4J forum?

In that case, I believe you can code this function yourself in a b4j program as user aggregate functions are available to you. Here's the javadoc.

 

[rgarnett1955]

Did I misread your post? I thought we were dealing with Android. Shouldn't your post be in the B4J forum?

In that case, I believe you can code this function yourself in a b4j program as user aggregate functions are available to you. Here's the javadoc.

Hi cimperia

I don't wish to be disrespectful, but I thought that sqLite issues would be applicable to all platforms.

When I ask a question I don't have any idea which forum it goes to. In fact I don't like the B4X search function at all. It has no advanced search, the search field is too small to contain anything more than two words. I end up asking questions that I shouldn't have needed to ask because the search is so broad, it gives you every post that happens to include words in the search field. In most cases you end up with six or seven pages of posts which don't contain the answer you are looking for.

I will take on-board your suggestion about the javadoc, but I would point out that I use B4X to program cross platform, because I don't want to program in Java, which is completely beyond me. If I could program in Java, why would I use B4X?

I thought the whole idea of B4X was so that cross platform programming was accessible to more people. In fact I think it does this exceedingly well. I really enjoy programming using it and have made some quite complex apps which do the job nicely. I aslo find the support from the website particularly good. Erel, Klaus and Don M. et al, do a Stirling job in responding to questions and concerns. I have been tutoring a colleague on the use of B4X and he is moving forward in leaps and bounds. He has trouble with bog standard C so I don't think he's quite ready to tackle java. B4X fits nicely with his VBA experince.

Best regards

Rob

 

[cimperia]

Hi Rob,

I appreciate your detailed answer, but what I said was very relevant. Maybe you got the wrong end of the stick.

SQLite on B4J is more flexible because you can access, through Java, SQLite functions which are not available under Android. So, if you're running a B4J app, you can implement the missing functionality; if you're running an Android app, you can't - as far as I know - as the required function is not available. But, as I mentioned, you can run in Android your own SQLite libraries.

My last word on this.

Good luck.

 

[Erel]

I don't wish to be disrespectful, but I thought that sqLite issues would be applicable to all platforms.

This is only partly correct, remember that B4X tools are native tools. B4A SQLite feature is based on the built-in OS SQLite engine.
There are many many things that you can do in one platform and can't do in a different one.
B4X empowers developers and doesn't limit them to the least common denominator.

 

[rgarnett1955]

Hi Rob,

I appreciate your detailed answer, but what I said was very relevant. Maybe you got the wrong end of the stick.

SQLite on B4J is more flexible because you can access, through Java, SQLite functions which are not available under Android. So, if you're running a B4J app, you can implement the missing functionality; if you're running an Android app, you can't - as far as I know - as the required function is not available. But, as I mentioned, you can run in Android your own SQLite libraries.

My last word on this.

Good luck.

Thanks for that.

I think I did get the the wrong end of the stick. It's so confusing and frustrating with all of the different languages and platforms, and databases. I know what I want to do, but doing it is a different thing.

Best regards

Rob