Showing posts with label SQLITE. Show all posts
Showing posts with label SQLITE. Show all posts

Friday, January 11, 2019

SQL Aggregate Function as Table using PHP

SQL aggregates as defined by netizens is "perform a calculation on a set of values to return a single scalar value". We often use aggregate functions with GROUP BY, HAVING clause of SELECT statements.

While it is very simple showing tables with just one aggregate or a set of aggregate functions using SELECT and GROUP BY then showing the table as retrieved by the SELECT statement, the table is practically vertical listing in nature. For example

SELECT states, people, SUM(revenue) FROM sales GROUP BY states,people order by states DESC

will show when you format it as table from the query result directly.

People   Revenue   State
Sam       100,000    Selangor
Ken        200,000    Selangor
Joe         100,000    Johor

What if you want to show all the people in the state regardless whether there are sales made by individual and the percentage of their Revenue for the state? It is quite impossible to do it in a single select statement.

The following method shows how to use a single statement to get a list of people and their revenue plus percentage of the group.

First we create an associative array from a list of people and populate it in the array.

$keys=array('Sam','Ken','Joe','April');
$values=array('Revenue'=>0,'Percent'=>0);
$total=0; //totals of the group
$revenue=array_fill_keys($keys,$values)

Retrieve the data using the previous SELECT statement like below. The example uses SQLite.

$db=new SQLite3('revenue.db');
$result=$db->query("select people, sum(revenue) from sales group by people");
while ($row=$result->fetchArray(SQLITE3_NUM))){
$ppl=$row[0];
$revenue[$ppl]['Revenue']=$row[1];
$total +=$row[1];
}
$db->close();
//do calculation and populate table
foreach ($revenue as $key=>$row){
$percent=round($row['Revenue']/$total*100,2);
echo " {$key}
{$row['Revenue']}{$percent}";}

Obviously, the above could be done by 2 queries. One to retrieve the overall total and the other one as above. However, what if you want to list the sales by date and show the people in columns? That is not an easy task representing it in one or two SQL.

You could retrieve the query by revenue grouped by date and people. Your array setting will be

$keys=array('Date','Sam','Ken','Joe','April');
$people=array_fill_keys($keys,$values)
$revenue=Array(); //you could fill the array with every date in the period you choose.

In the while loop, do as follows (assuming date is unix datetime),

$rdate=$row[0]; // the date field
if (!isset($revenue['A'.$rdate])){
$revenue['A'.$rdate] =$people;
$revenue['A'.$rdate]['Date']=date('m/d/Y',$$rdate);
) // set up the array and populate the 'Date' of the array for easy retrieval later
$people = $row[1] //the people field
$revenue['A'.$rdate][$people]=$row[2]; // the revenue field

With this you could populate the whole columns of people with date as first column. Displaying the table is then a breeze using the FOREACH example above.



Saturday, December 29, 2018

SQLite syncing with remote data to act as local data

There is a need to get a set of data from a remote server and distribute to a group of local computers. The data must not be deleted then inserted. The data also may not be available on remote server due to it being closed and removed.

If the data cannot be deleted (truncated) then the only way is to do "insert or ignore" so that existing data is either updated if it exists or inserted if it is not. Meanwhile, there is a problem of remote data does not exist while local data exists.

Well, it is quite easy. Two tables are used. One for the main table with a key index field and the other one only the key index field. Both table index uses the same field.

When importing data, both tables are inserted with the remote data. The second table was truncated before the import starts. This means the main table contains new and old data while the second table only contains the new data. At the end of importing, just simply delete from the main table where the index field not exists in second table. Thus only the imported data remains in the main table.

Example of the "insert or ignore" goes like this

Insert or ignore on maintable (f1, f2,f3....) values ('x1', 'x2, 'x3'...);update maintable set f2='x2', f3='x3' where f1='x1'

It seems that the above will insert and then update if the index is not found but it is still better than using two sql statements to insert or update plus one statement to check whether the index exists before doing either insert or update statement.

Example of the delete command goes like this

delete from maintable where  f1 not in (select f1 from secondtable)

The main table can then be access by local users.



Wednesday, December 26, 2018

Triggers in SQLite

Although triggers are common in databases. I seldom use it. Recently there is a need to pre-check before an insertion whether the number of records in a certain condition is within the quota set. Also, there is a need to check whether the specific field value exists already. Finally, there is a need to check whether one field content is in the list of another.

Under normal condition, I would set up three queries to check for it before doing the insertion of records. It will be untidy to code if this is done the normal way. Triggers then is the more cleaner way to do so.

The setting up is quite straight forward.

Create trigger before insert on for each row begin select

It then followed by the list of conditions

case when ((select....) > ) then raise(abort, "error message")
when ((select from where = NEW. ) is not null) then  raise(abort, "error message")
when ((select from where = NEW.) is not null) then raise(abort, "error message") end;
end;

In the script we just need a normal insert SQL with a exception check, (PHP try and catch) to get the error message. Just get the different message and act accordingly. It is very much cleaner in coding without much coding in the application.

This trigger can be inserted externally using normal SQL procedure. It can also be removed the same way. Obviously, same name trigger must be removed first before insert.

There are much more type of triggers. This one only talks about INSERT BEFORE.



Sunday, August 12, 2018

SQLITE query hourly data over a date range

There was a request to get monthly summary data in a half hourly period. This is to allow user to view the month's data by the half hour range. For example, user need to know what is the volume of customer coming in at which period so that they could prepare to have enough staff to cater to the inflow.

The following is assuming the date is stored as UnixTimeStamp.

The first task is to get the half hour period using strftime.

strftime('%H',DateCreated,'unixepoch','localtime') as Hour, case when cast(strftime('%M',DateCreated,'unixepoch','localtime') as integer) <30 as="" else="" end="" minute="" p="" then="">
Don't forget to get the count

,count(Customer) as Count

Of course you must define the table

from Customer

Next thing to set is the date range as month.

where strftime('%Y-%m-%d',DateCreated,'unixepoch') between '2018-08-01' and '2018-09-01'

We also want to limit the time to 10am-10pm

and strftime('%H:%M:%s',DateCreated,'unixepoch','localtime') between '10:00:00' and '23:00:00'

Finally group by the hour and minute

group by Hour, Minute

The result is you get 3 columns Hour, Minute, Count like

10 00 5
10 30 1
11 00 95
,..

Obviously, at a certain time, there will be no data thus the specific time period will be missing. A good way is to create an associative array with all the time defined and the value of Count set as 0. Use Hour, Minute as the key like '1000'. Update the count using the keys from the result. In this way a complete list of the period will be available.