sql - Delete null values until first value is not null -


I have daily schedules for companies in datasets and use PostgreSQL.
For every company, all the rows with zero in column 3 will be removed for this company until the first time the tap is entered in this column. Then all the constant missing values ​​are filled with the value of the value of the last value for this company which is not faucet.

You can visualize the following example data: 

  Date Company's column 3 1 2004-01-01 A5 2 2004-01-01 B null 3 2004 -01-01 c Nal 4 2004-01-02 A. Nal 5 2004-01-02 B 7 6 2004-01-02 Si Naul 7 2004-01-03 A6 8 2004-01-03 B97 2004-01 -03C910 2004-01-04 A null 11 2004-01-04 B. Noull 12 2004-01-04 Sea Null

It would be nice if I write a query that delivers 

  Date Company's column 3 1 2004-01-01 A5 2 2004-01-02 A5 3 2004-01-02 B 7 4 2004-01-03 A6 5 2004 -01-03 B 7 6 2004-01-03 C 9 7 2004-01-04 A6 8 2004-01-04 B97 2004-01-04 C 9

I Tried: 

  SELECT a.date, a.company, COALESCE (a.column3, (SELECT b.column3) is not a null order by Mercantile B WHERE b.company = a.company and b.colmun3b company = A.company DESc limit 1)) from mytable A;

There are two problems with the code:

  1. This does not remove all records with tap values, as long as the first tap value, but / fill Has all the missing values ​​in
  2. ... not with the first observation in the column and with the last overview before the missing value.

I suggest using two subquery levels instead of correlated subqueries:

> SELECT the_date, company, max (call 3) over (alliance by company) AS to 3 (select *, count (colle 3) over (company order by the detergent) Group FRTBL) All 1, sub-2 where 3 columns are not less than zero; Company

produces the requested result.

This copy code <(code, (DDT), something should be faster for tables with more than symbols A ( exclusive ?) Index will help a lot to implement specificity: 

  create index tbl_company_date_idx ON tbl (company, d_dt);

how?

total function count () while calculating Ignoring zero values ​​is used as a composite window function, this is the default window definition of moving column Does not, which is the incompatible pricing and the current row between the and the result is being "trapped" for the rows with null values, which creates a peer group which is similar ( Non-zero) value.

In the second window function, only non-empty values ​​per group are easily extracted from maximum . Prior to the non-Rick Worl, the group retains the tap, which easily ends in the final selection.

More clarification in this closely related answer:


Comments

Post a Comment

Popular posts from this blog

eclipse plugin - Run java code error: Workspace is closed -

To create an automated project, I created a plug-in project with the following dependencies: Org.eclipse.core.resources org.eclipse.equinox.registry org.eclipse.core.runtime and the following The Java class is located in the src folder: Package Examiner; Import org.eclipse.core.resources.IProject; Import org.eclipse.core.resources.IWorkspaceRoot; Import org.eclipse.core.resources.ResourcesPlugin; Import org.eclipse.core.runtime.CoreException; Import org.eclipse.core.runtime.IProgressMonitor; Import org.eclipse.core.runtime.NullProgressMonitor; Public class tes {public static zero main (string [] args) {// TODO auto generated method stub IProgressMonitor progress monitor = new NullProgressMonitor (); IWorkspaceRoot root = ResourcesPlugin.getWorkspace (). GetRoot (); Ipoject project = root.jetproject ("desired projectname"); Try {Project.create (progress monitor); Project.open (progressMonitor); } Grip (CoreException E) {// TODO Auto generated blocking block e....
Read more

ios - How do I use CFArrayRef in Swift? -

I am using Objective-class in my Swift Project through a bridging header. The method signature looks like this: - Some cement (some type) some parameters; I started by getting an example of class, calling method, and storing the value: var myInstance = MyClassWithThatMethod (); Var cfArr = myInstance.someMethod (some value); Then try to get the value in the array: var valueInArrayThatIWant = CFArrayGetValueAtIndex (cfArr, 0); However, I get the error unmanaged & lt; Cfarray & gt; Not like 'CFArray' . Unmanaged & lt; Cfarray & gt; also means? I looked, but I do not need to change the array in a fast array (though it would be good). I need to be able to get value from the array. I also tried CFArray method to pass in a function: func doSomeStuffOnArray (myArray: NSArray) {} Although I get the same error when using: doSomeStuffOnArray (cfArr); // unmanaged & lt; CFArray & gt; I am using CFArray because I want to sto...
Read more

scala - Play Framework - how to bind form to a session field -

Is there any way, I can get some parameters from the header, cookies (log in userId in my case) , And then apply it in a form which I know who will deposit the ticket? SupportForm supportForm: form [supportTicket] = form (mapping ("question" -> text, "priority" -> text) (apply support ticket. (HelpText.update) What are the good practices here? What is the call to apply the request, when I can use it (and also a good practice?) Edit An issue, absolutely deceiving anyone if I were to create a hidden area with this value. It could Ript, but the issue may be re-used in any way to verify and return the form, it can not be sure how it ....
Read more