sql - Group Date column based on hours -

-

I have a table in the SQLite database where I store data about call logs as an example That my table looks like this 

  | Call_count | Call_best Time_slice | Time_Stamp | | 10 | 500 21 | 139936 9 26 9. | 2 | 88 | 22 | 1399383668 |    
  Calls_count  are calls made since last comments  
  call_department  from previous inspections The duration of the call in MS  
  Time-piece  indicates a time portion of the week every day divided into 4 parts of 6 hours such as  
  06: 00-11: 59 | 12: 00-17: 59 | 18: 00- 23.59 | 24: 00-05: 59 | Mon 11 | 12 | 13. 14 | Mars | 21 | 22 | 23 24 | Mercury | 31 | 32 | 33 | 34 Guru 41 | 42 | 43 | 44 | Fri | 51 | 52 | 53 | 54 Saturn | 61 | 62 | 63 64 | Sun | 71 | 72 | 73 | 74 |  
 
 and  time_stamp  is unix era when the observation was done / record was put into the database 
 
 Now I want to make a query so That if I specify the beginning of time and the end of the week, the results give 168 lines of data, give me the amount of calls grouped per hour, just like I get 24 rows for every week. This is a break of one hour of call within a week. 
   SUM_CALLS | Deadline | Hour_of_Week | 10 | 11 | 1 | 0 | 11 | 2 | .... 7 | 74 | 167 | 4 | 74 | 168 |

In the above example of the desired result, 

  First line Monday 06: 00-06: 59 second line Monday 07: 00-07: 59 last line Sunday 04 is: 00-05: 59

SQLite common table from version 3.8.3 Expression is supported and it is in the form of comfortable hours (X, Y) with possible solution 

  (selection cast ('STRFTIME ('% s ', STRFTIME ('% Y- % M-% D% H: 00: 00 ',' 2014-05-05 00:00:00 ')) ANTEGER), Cast (STRFTIME ('% s', 'S' As per RFTME ('% Y-% M-% D% H): 59: 59', '2014-05-05 00:00:00')), Union All will be selected from X + 3600, Y + 3600 hours LIMIT 168) Select COALESCE (SUM (Calls_count), 0) AS SUM_CALLS, Case Cast (STRFTMEE ('% W', X, 'UNIXPOX') Antigar) When 0 then 7 ELSE STRFTME ('% W', X, 'UNIXPOK') END || When STRFTIME ('% H:% M:% S', x, 'unixepoch') between '06: 00: 00 'and '11: 59: 59' then 1 when STRFTIME ('% H:% M:% Between '12: 00: 00' and '17: 59: 59 'then 2 when between STRFTIME ('% H:% M:% S ', x,' unixepoch '),' S ', X,' UnixPok ') '00: 00: 00' and '23: 59: 59' then 3 '00: 00: 00' and '05 between SRRFTME ('% H:% M:% S', X, 'UnixPoc') : 59: 59 'then' Time_Slice 'as the 4th, join call_logs at hours left call_logs.time_stamp & gt; (x- (select min (x) hours)) / 3600 +1 hour_of_week form; = Hours.x and call_logs.time_stamp & lt; Hour hour_of_week by Hours_off_week by hours.y;

This SQLite version 3.7.13 has been tested with CTE: 

  Hide existing points; Select Select as the temporary visual digit Choose the union D. 1 Select the union 2 Select the union 3 Select the union 4 Select the union 5 Select the union 6 Select the union 7 Select the association 8 Select the union 9; Skip the current hours; Select Create Temporary View Time STRFTIME ('% s', '2014-05-05 00:00:00') + S AS X, STRFTMEE ('% s', '2014-05-05 00:00:00') + 3599 from Ace (Select (advertising || BD || CD) * 3600 from ASF score, number B, number C seam border 168); Selection COALESCE (Counts_count), 0) SUM_CALLS, CAST CAST (as strftime ('% w', x, 'unixepoch') as integer) when 0 then 7 and the strftime ('% w', x , 'Unixepoch') END || || When STRFTIME ('% H:% M:% S', x, 'unixepoch') between '06: 00: 00 'and '11: 59: 59' then 1 when STRFTIME ('% H:% M:% Between '12: 00: 00' and '17: 59: 59 'then 2 when between STRFTIME ('% H:% M:% S ', x,' unixepoch '),' S ', X,' UnixPok ') '00: 00: 00' and '23: 59: 59' then 3 '00: 00: 00' and '05 between SRRFTME ('% H:% M:% S', X, 'UnixPoc') : 59: 59 'then' Time_Slice 'as the 4th, join call_logs at hours left call_logs.time_stamp & gt; (x- (select min (x) hours)) / 3600 +1 hour_of_week form; = Hours.x and call_logs.time_stamp & lt; Hour hour_of_week by Hours_off_week by hours.y;

Comments

Post a Comment

Popular posts from this blog

Pass DB Connection parameters to a Kettle a.k.a PDI table Input step dynamically from Excel -

-
Image
I have a requirement whenever I run my own kettle, dynamically took database connection parameters from an excel source Every run should go. I have an Excel column names: hostname, username, database, password. I want to pass these connection parameters to my table input stage dynamically whenever it runs the job. This is what I was trying to do. You can get it Connection parameters from the DB reading source (for example Excel or a CSV file in my example) < / Li> Store the parameters in the variable Using the variable in your connection's settings. Setting Variables Create another conversion for (you can not do it in the same conversion Uses): set variables element variable: The element that reads / writes your data creates a new connection and sets the connection parameter using $ {variable name_} Note that you will need to type in the field field in the field $ {password} Also keep in mind that this can be a security problem as the
Read more

multithreading - PhantomJS-Node in a for Loop -

-
I am trying to implement a screen-scraps application that opens a URL in which a parameter changes as it is for a loop: var data = [ '100', '101', '102', '103', '104']; (Var indexA = 0; indexA & lt; data.length; indexA ++) for {Phantom Generation ({'port': freeport}, function (pH) {ph.create page (function (page) {page.open ("http: //...++ data [index B] +" ... " , Status {console.log ("open site:" + data [index B], position); / * OLD * / indexB ++; page evaluation (function () {// page scrape and an object Return}, function {console.log (result.dataRequest); // here is the problem}); / * new and fixed * / indexB ++} first console Logs it: Open Site: 100 Success Opens Site: 101 Success B Ola Site: 102 Success Opening Site: 103 Success Opened Site: 104 Success Which is good. Data is verified in scrap HTML and from index to array Matches. So in page 101 I scrap '101 and retrieve' and pu
Read more

c++ - MATLAB .m file to .mex file using Matlab Compiler -

-
I know that it can speak as a very famous topic, but something that helps me with it Not found This scenario is: I have my functions function, no matter what's inside, it's a generic function that may depend on other toolboxes. Does. Has input and output; I want to generate the same myFun.mex function; : Lib myFun This way I get some new files: libmyfun.c libmyfun.dll Libmyfun.exp libmyfun.exports libmyfun .h libmyfun.lib Reading around and I think that Creating a myfun.cpp that is very famous is included: / * gateway function * / zero max function (ent NLHS, MXARRA * PLS [], int NRHS, CONST M Xeray * PHS []) Here is the Variable Deals * / / * code here * /} I have tried to do this and are called Regular of Max Generation: Max ('- v', 'myfun.cpp') But I get many errors, and I should say that my myfun.cpp copy / Paste Function is where I have a lot of doubts ... especially on input / output management. My q
Read more