Find date sequence in PostgreSQL -

-

i'm trying find maximum sequence of days customer in data. want understand max sequence of days specific customer made. if enter app in 25/8/16 , 26/08/16 , 27/08/16 , 01/09/16 , 02/09/16 - max sequence 3 days (25,26,27).

in end (the output) want 2 fields: custid | maxdaysequence

i have following fields in data table: custid | orderdate(timestemp)

for exmple:

custid  orderdate 1   25/08/2007 1   03/10/2007 1   13/10/2007 1   15/01/2008 1   16/03/2008 1   09/04/2008 2   18/09/2006 2   08/08/2007 2   28/11/2007 2   04/03/2008 3   27/11/2006 3   15/04/2007 3   13/05/2007 3   19/06/2007 3   22/09/2007 3   25/09/2007 3   28/01/2008

i'm using postgresql 2014.

thanks

trying: 

select custid, max(num_days) longest  (         select custid,rn, count (*) num_days         (            select custid, date(orderdate),                    cast (row_number() on (partition custid order date(orderdate)) varchar(5)) rn             table_        ) x group custid, current_date - interval rn|| ' day'  ) y group custid

try:

select custid, max( abc ) max_sequence_of_days (   select custid, yy, count(*) abc   (     select * ,           sum( xx ) on (partition custid order orderdate ) yy     (       select * ,           case when                orderdate - lag( orderdate ) on (partition custid order orderdate )                <= 1          0 else 1 end xx       mytable     ) x   ) z   group custid, yy ) q group custid

demo: http://sqlfiddle.com/#!15/00422/11

===== edit ===========

got "operator not exist: interval <= integer"

this means orderdate column of type timestamp, not date.
in case need use <= interval '1' day condition instead of <= 1:

please see link: https://www.postgresql.org/docs/9.0/static/functions-datetime.html learn more date arithmetic in postgresql

please see demo: http://sqlfiddle.com/#!15/7c2200/2

select custid, max( abc ) max_sequence_of_days (   select custid, yy, count(*) abc   (     select * ,           sum( xx ) on (partition custid order orderdate ) yy     (       select * ,           case when                orderdate - lag( orderdate ) on (partition custid order orderdate )                <= interval '1' day          0 else 1 end xx       mytable     ) x   ) z   group custid, yy ) q group custid

Comments

Post a Comment

Popular posts from this blog

asp.net - How to correctly use QUERY_STRING in ISAPI rewrite? -

-
i'm having difficult time , hope guys can help. using helicon isapi rewrite version 3.1.0.104 on our iis server. edit http.conf file , have tried , still fail. this trying do: redirect url: https://www.domain.com/switch-by-version?version=2.8.5.2594 to: https://test.domain.com/load/load.aspx?tver=2.8.5.2594 the version number @ end source url change, , need target url have same version number @ end of url example above shows. i tried following not work: rewritecond %{query_string} ^version=(\d\d?)\.(\d\d?)\.(\d\d?)\.(.*)$ [nc] rewriterule ^/switch-by-version(.*)$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l] any appreciated! thanks. if it's running in equivalent context of .htaccess , rule shouldn't start / . here's simpler version: rewritecond %{query_string} ^version=(\d+\.\d+\.\d+(?:\..+)?)$ [nc] rewriterule ^switch-by-version/?$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l]
Read more

jsf - "PropertyNotWritableException: Illegal Syntax for Set Operation" error when setting value in bean -

-
this question has answer here: how send form input values , invoke method in jsf bean 1 answer i error when trying send values jsf page bean. i want set values fields in bean, create airplane instance , add database. error telling me use illegal syntax setter part of bean. why error? didnt find on site helped me problem. here jsf part: <h4>add plane</h4> <c:if test="#{not empty listairplanebb.error}"> <div class="alert alert-danger"> <span class="glyphicon glyphicon-remove"></span> <h:outputtext value="#{listairplanebb.error}" /> </div> </c:if> <h:form> <table class="table"> <thead> ...
Read more

laravel - Undefined property: Illuminate\Pagination\LengthAwarePaginator::$id (View: F:\project\resources\views\admin\carousels\index.blade.php) -

-
when try pass variable data view error, can't find document problem controller(carouselcontroller.php) public function index() { $carousels = carousel::orderby('created_at', 'asc')->paginate(12); return view('admin.carousels.index')->withcarousels($carousels); } my view(index.blade.php) <div class="row"> <div class="col-md-9"> <h1>all images</h1> </div> <div class="col-md-3"> <a href="{{ route('carousels.create') }}" class="btn btn-lg btn-block btn-primary ">create new carousel</a> </div> <div class="col-md-12"> <hr> </div> </div>{{-- end of row --}} <div class="row"> <div class="col-md-12"> <div class="row"> @foreach($carousels $photo) <div class="col-xs-6 col-md...
Read more