CodePudding user response:
Like '%' + $P {param} + '%'CodePudding user response:
LIKE '%' | | $P {param} | | '%' can I write soCodePudding user response:
To ask, I also met this problem, the landlord to find a way?CodePudding user response:
A column in the Oracle turned example- a 1 | 2 | 3
To:
A 1
A 2
A 3
Create table test (a1 varchar2 (10), b1 varchar2 (10));
Insert into test (a1, b1) values (' a ', '1, 2, 3);
commit;
- the method 1 `
The select a1, regexp_substr (b1, '[^,] +' 1, level)
From the test
Connect by level & lt;=length (b1) - length (the replace (b1, ', ')) + 1
- the method 2
The select a1, regexp_substr (b1, '[^,] +' 1, level)
From the test
Connect by level & lt;=length (regexp_replace (b1, '[^,] +')) + 1
- method 3
The select a1, regexp_substr (b1, '[^,] +' 1, level)
From the test
Connect by level & lt;=regexp_count (b1, ', ') + 1
CodePudding user response:
- 24 hours a day if the statistics the amount of data per hour- create a test table
The create table hour_test (add_hour date);
- circular insert test data
Declare the n number.
The begin
N:=0;
While n<50000 loop
N:=n + 1;
Insert into hour_test
Select * from (
Select rownum rn, to_date (' 2017-01-01 ', 'yyyy - - dd hh: mm mi: ss') + rownum/24 add_hour
The from dual
Connect by rownum<25
) a
Where a.r n=(select round (dbms_random value (1, 24)) from dual);
end loop;
commit;
end ;
- results of a query
The select to_char (add_hour, 'hh24) and count (*) n the from hour_test
Group by to_char (add_hour, 'hh24')
The order by to_char (add_hour, 'hh24');
CodePudding user response:
Some Tableau development summary1. The proportion: corresponding measures on the field, add table of
Calculation type selection "of" total percentage, collect value range to choose "table (down)"
Reference: the professional companies T + 0 product accounts statistics and analysis - the registration time (table)
Figure 2. The area of the registration time highs and lows
(1) add the registration time (month) (measure)
CUST_CNT_MONTH: {FIXED [customer registration time (month)] : SUM ([CUST_CNT])}
(2) adding the maximum and the minimum (measure)
{EXCLUDE [customer registration time (month)] : MAX ({FIXED [customer registration time (month)] : SUM ([CUST_CNT])})}
{EXCLUDE [customer registration time (month)] : MIN ({FIXED [customer registration time (month)] : SUM ([CUST_CNT])})}
(3) add a computed field, high and low (measure)
IF [CUST_CNT_MONTH]=[CUST_CNT_MAX] OR [CUST_CNT_MONTH]=[CUST_CNT_MIN]
THEN [CUST_CNT_MONTH] ELSE NULL END
(4) to compute the field measurements of draw a diagram, select the "circle", and then click on the biaxial
Reference: the professional companies T + 0 product accounts statistics and analysis - the registration time (line chart)
3. Don't statistical classification problem for age distribution (for example)
(1) the new calculated fields (dimensions) AGE_LEVEL (add level definition)
IF [the AGE] <18 THEN '& lt; 18 '
ELSEIF (AGE) & gt; 18 AND=[AGE] <25 THEN '[18, 25)
ELSEIF (AGE) & gt;=25 AND [AGE] <30 THEN '[25, 30)
ELSEIF (AGE) & gt;=30 AND [AGE] <35 THEN '[30, 38)
ELSEIF (AGE) & gt;=35 AND [AGE] <40 THEN '[35 or 40)
ELSEIF (AGE) & gt;=40 AND [AGE]
=45 '
(2) add all age customer number field (measure)
CUST_CNT_AGE: CUS_CNT
(3) the CUST_CNT_AGE add table calculation: for ages of
Calculation type selection "of" total percentage, collect value range to choose "table (down)"
(4) the statistical measure of the number of the number of each age stage)
CUST_CNT_TOTAL: {EXCLUDE [AGE_LEVEL] : SUM ([CUST_CNT])}
(5) to add the number of male/female calculated fields (measure)
CUST_CNT_FEMALE: IF [GENDER]='female' END THEN [CUST_CNT] ELSE 0
CUST_CNT_MALE: IF [GENDER]='male' END THEN [CUST_CNT] ELSE 0
(6) new two metrics calculated fields (measurement), statistics of all ages the number of male/female
CUST_CNT_AGE_FEMALE: {FIXED [AGE_LEVEL] : SUM ([CUST_CNT_FEMALE])}
CUST_CNT_AGE_MALE: {FIXED [AGE_LEVEL] : SUM ([CUST_CNT_MALE])}
(7) the new two metrics calculated fields (add the number of male/female ratio)
CUST_CNT_FEMALE_PERCENT: [CUST_CNT_AGE_FEMALE]/[CUST_CNT_TOTAL]
CUST_CNT_MALE_PERCENT: [CUST_CNT_AGE_MALE]/[CUST_CNT_TOTAL]
Reference: the professional companies T + 0 product accounts statistics and analysis - the registration time (line chart)
The use of - DATETRUNC
Yesterday: DATETRUNC (' day ', NOW () - 1)
Yesterday the data: [statistical date]
-- NOW () on (date) (month) (year) + minutes
- TODAY () to (date) (month) (year)
- add {} the aggregation function and the aggregation function matching: statistical date] [={MAX (statistics [date])}
- take the latest date by default
IF statistics [Date]={MAX (statistics [Date])} THEN 'Last Date'
The ELSE
STR (DATEPART (' year 'statistics [date])) +' - '+ STR (DATEPART (month, date] [statistics)) +' - '+ STR (DATEPART (' day' statistics [date]))
END
- take the latest date of 2 by default
Create a calculated field 1: {FIXED [Time] : Max (year ([Date]) * 100 + month ([Date]))} - [month]
Create calculated fields: 2 year ([Date]) * 100 + month ([Date]] - [latest month]
Create a string parameters: [whether] the latest date, the default value is' no '
Create a computed field 3: [whether] the latest date="no" or [Month]=[latest Month]
Then statistical date [] and [whether] the latest date to filter.
- total sorting:
IF [migration path]='total' THEN one ELSE 0 END; Then editor of the collation of migration paths, select "total order" field, click on the "minimum"
- negative highlight:
IF LEFT ([field], 1)='-' THEN one ELSE 0 END THEN the drag and drop the color, highlight the "negative" field editor shows the target color can
Duplicate values highlighted: IF {FIEXD [computed field] : COUNT ([computed field])} & gt; 1 THEN 1 ELSE 0 END
- negative - removed, replaced by () shows:
IF LEFT ([field], 1)='-' THEN '(' + REPLACE ([field],' - ', ') + ') 'the ELSE [field] END to REPLACE the previous field
CodePudding user response:
Like '%' | | $P {param} | | '%' try it on