The diagram below, is a securities company's agent, commission, commission scheme by brokers signing and sales department to discuss, so the whole company down, commission will have a variety of piecewise way, but general sales will follow certain principle, make sure do not make segmentation way too much,
Now my project team, to replace the vendor before the old system and old system for everyone to write a cut section, the new system, we make up planning commission segments in different ways, and then everyone to correspond to the corresponding section, reduce the redundant data, now want to statistics the segmented in many ways, but want to for a long time can't think of statistical SQL, turn to god for help,

CodePudding user response:

Figure, five agent, using the two commissions, is want to statistics out this effect,

CodePudding user response:

Don't read

CodePudding user response:

refer to the second floor js14982 response:

understand

May be on business of time I say a little more cumbersome,

Is actually in the statistics of how many red box circle,
A red box on behalf of a fragment, the same segmentation way mean, 1 step, such as the above red box are points 5, 2. Each leg of the upper and lower limits of commission ratio should be the same.

CodePudding user response:

 select count (distinct fd) from 
(select agent code, WMSYS WM_CONCAT (ceiling - floor every block) fd 
The from the TAB 
Group by agent code); 

You can consider to combine each agent piecewise line up and down into a line, to deal with again

CodePudding user response:

The

reference 4 floor js14982 reply:

 select count (distinct fd) from 
(select agent code, WMSYS WM_CONCAT (ceiling - floor every block) fd 
The from the TAB 
Group by agent code); 

Can consider to combine each agent piecewise line up and down into a line, then to handle

I considered this way, but will not appear such circumstance

Transfer line column ever encountered such a situation
The name X
A 1
A 2
A 3
B 1
2 B
3 B
WM_CONCAT (X) is 123 for A deposit, deposit for B data is 312

CodePudding user response:

Mark...

CodePudding user response:

You want fixed order with listagg, can also combine corresponding proportion field is also more accurate point, listed in the output

 select count (distinct fd | | fd1) from 
(select agent code, listagg (ceiling - floor every block) within group (order by piecewise ceiling - segmentation threshold) fd, 
Listagg (corresponding) within group (order by corresponding proportion) fd1 
The from the TAB 
Group by agent code);