aggregate functions - Time series in PostgreSQL - list changes -

-

I have a table in PostgreSQL with some time-series data, which is showing the change of values ​​in the parameter - we There can be no difference and there is date_from related to each date_to (excluding the last course): 

  select date_from , Date_to, xx yy orders from date before date; Date_from | Date_to | Xx ----------- + ------------ + ------------------ 2014-05-01 | 2014-05-21 | 0/05/2014 | 2014-06-02 | 10 2014-06-02 | 2014-06-05 | 10 2014-06-05 | 2014-06-10 | 10 2014-06-10 | 2014-06-26 | 0 0 0 0 0 6 2014-07-01 | 10 2014-07-01 | 2014-07-08 | 10 2014-07-08 | 2014-07-23 | 0 2014-07-23 | 2014-08-04 | 10 2014-08-04 | 2014-08-20 | 10 2014-08-20 | 2014-09-02 | 20 2014-02-02 | 2014-09-03 | 20 2014-09-03 | 2014-09-22 | 02 2014-09-22 | 2014-09-24 | 10 2014-09-24 | 2014-10-02 | 20 2014-10-02 | 2014-10-08 | 20 2014-10-08 | Infinity | 0   
 My goal is to collect data based on the time interval, and it indicates which code  xx  remains on a given value:  
  from date_ | Date_to | Xx ----------- + ------------ + ------------------ 2014-05-01 | 2014-05-21 | 0/05/2014 | 2014-06-10 | 10 2014-06-10 | 2014-06-26 | 0 0 0 0 0 6 2014-07-08 | 10 2014-07-08 | 2014-07-23 | 0 2014-07-23 | 2014-08-20 | 10 2014-08-20 | 2014-09-03 | 20 2014-09-03 | 2014-09-22 | 02 2014-09-22 | 2014-09-24 | 10 2014-09-24 | 2014-10-08 | 20 2014-10-08 | Infinity | 0   
 To present this data in a more compact way for some business users, I tried to use but I did not know how to separate those changes Is there any sign of anyone?  
 Thanks,   
 
 
 You can use the walking to create group identifier Total feature, such as:  
  with yy (date_from, date_to, xx) AS (VALUES ('2014-05-01': Date, '2014-05-21': Date, 0), ('2014-05-21', '2014-06-02', 10), ('2014-06-02,' '2014-06-05', 10), ('2014-06-05 ',' 2014-06-10 ', 10), (' 2014-06-10 ',' 2014-06-26 ', 0), (' 2014-06-26 ',' 2014- 07-01 ', 10), ('2014-07-01', '2014-07-08', 10), ('2014-07-08', '2014-07-23', 0), ('2014-07-23 ',' 2014-08-04 ', 10), (' 2014-08-04 ',' 2014-08-20 ', 10), (' 2014-08-20 ',' 2014- 09-02 ', 20), ('2014-09-02', '2014-09-03', 20), ('2014-09-03', '2014-09-22', 0), ('2014-09-22 , '2014-09-24', 10), ('2014-09-24', '2014-10-02', 20), ('2014-10-02', '2014- 10-08', 20 ), ('2014-10-08', 'A Nantta, 0) Date from date, date, time, xx, amount (g) to date (date from date to date) GRP (selection date_, date_to, xx), when in case (xx) over (date Date from date) = xx then zero to 1 and g i.e. date from date;   
 As soon as you have a new column  grp , you can use it in  GROUP BY . Note, that subcategory is needed, because in some other way the window can not refer to the results of the actions.  
 Please also check this.

Comments

Post a Comment

Popular posts from this blog

php - PDO bindParam() fatal error -

-
I am trying to learn PDO, now I have written this small code, but it gives me a serious error: Fatal error: Call a member function at a non-object dot () ... $ con = new mysqli ("127.0.0.1", "root", "", "csvdangercheck"); $ Query = $ con- & gt; ("Id,` var1`, `var2`) values ​​(" id,: var1,: var2) ";); $ query- & gt; bypass pattern (': id', $ id); $ query- & gt; BindParam (': var1', $ VAL1); $ Query- & gt; bindParam (': var2', $ val2); $ Query-> Executed (); I Trying to use print_r ($ con-> errorInfo ()) but Fatal error: Undefined method mysqli :: errorInfo ( ) ... can someone tell me what am I missing? Flaffeh said, you have PDO Missing Mysqy, try it: $ con = new PDO ('mysql: host = 127.0.0.1; dbname = csvdangercheck', 'root', '');
Read more

php - How can I cram 6+31 numeric characters into 22 alphanumeric characters? -

-
I have a 6 digit number and a 31-digit number (such as "234536" and "201103231043330478311223582826") that I The API needs to be crawled in the same 22-character alphanumeric field that uses PHP. I tried to convert each one to 32 (using custom code could not handle the base_convert () large number) and to connect with a single-character delimiter, but It only gives me 26 characters This is a restore API, so the letters should be URI-protected. I would like to do a database table cross with two references without reference to another reference value, if possible, any suggestions? Instead use a radix of 62. This will give you 22 characters for the top, 3.35 for the pre and 17.3 for the next. & gt; & Gt; & Gt; Math.log (10 ** 6) /math.log (62) 3.3474826039165504 & gt; & Gt; & Gt; Math.log (10 ** 31) /math.log (62) 17.295326786902177
Read more

logging - How can I log both the Request.InputStream and Response.OutputStream traffic in my ASP.NET MVC3 Application for specific Actions? -

-
For a specific set of tasks, I need to log in to the incoming request inputstream as well as outgoing response. is. I am using an ActionFilterAttribute for this and overriding the OnActionExecuted and OnResultExecuted methods. So I'm starting with this idea ... Public category ActionLoggerAttribute: ActionFilterArrbit {Public Override Zero On-Offed Execution (ActionActioned Contact Filter Filter) {base.OnActionAxecuted (FilterContext); HttpRequestBase request = filterContext.HttpContext.Request; // TODO: Log the request. InputStream} Public Override Zero OnResultExecuted (ResultExecutedContext FilterContext) {base.OnResultExecuted (filterContext); HTTPRPSPointBase Response = FilterContacts Hpptex response; // TODO: Log on Response.OutputStream}} Ideally I'll hook it up with enterprise library logging because I'm already using it for error logging. Am I reaching the input and output stream at the right time? Can I easily use the Enterprise Library to log i...
Read more