| Puzzle n°09 - Finding a query for sine function distribution ** [message #186198] |
Sun, 06 August 2006 23:35  |
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
|
Member |
|
|
Let me tell you something about distribution function.
Here, i use the term, 'distribution function' differently from what we use in general mathematics.
See the following query which generates self-counting sequence.
SELECT TRUNC (1 / 2 + SQRT (2 * LEVEL)) level#
FROM DUAL
CONNECT BY LEVEL <= 1000
LEVEL#
------
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
...
...
...
and, if we add some group by and counting to the above query...
SELECT level#
, LPAD ('*', COUNT (*), '*')
FROM (SELECT TRUNC (1 / 2 + SQRT (2 * LEVEL)) level#
FROM DUAL
CONNECT BY LEVEL <= 1000)
GROUP BY level#
we can see a graph like below
LEVEL# LPAD('*',COUNT(*),'*')
------ ----------------------
1 *
2 **
3 ***
4 ****
5 *****
6 ******
7 *******
8 ********
9 *********
10 **********
11 ***********
12 ************
13 *************
14 **************
15 ***************
16 ****************
17 *****************
18 ******************
19 *******************
20 ********************
...
...
...
As we all know, this is like a function f(x) = x.
i use the term, 'distribution function' as this meaning!
Distribution of numbers of data values.
Now, my question is this.
Find a query that generates distribution function f(x) = sin(x)
(Here, any amplitude and any period of sine function is allowed except zero.
And only the form of sine function is important.
No subquery factoring or user-defined function or procedure is allowed.
Use simple select statement only.)
And now, have a good challenge!
SELECT level#
, LPAD ('*', COUNT (*), '*')
FROM (
-------- some query -------------------
)
GROUP BY level#
LEVEL# LPAD('*',COUNT(*),'*')
------ ----------------------------------------------------
0 *
1 **
2 *******
3 *************
4 *****************
5 **********************
6 **************************
7 ******************************
8 **********************************
9 **************************************
10 ****************************************
11 ********************************************
12 *********************************************
13 ************************************************
14 *************************************************
15 *************************************************
16 **************************************************
17 **************************************************
18 *************************************************
19 ************************************************
20 ***********************************************
21 ********************************************
22 ******************************************
23 ***************************************
24 ***********************************
25 ********************************
26 ****************************
27 ************************
28 *******************
29 **************
30 *********
31 *****
Query Your Dream & Future at
http://www.soqool.com
[Updated on: Thu, 14 February 2008 03:26] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: query quiz> find a query for sine function distribution [message #223747 is a reply to message #223740] |
Sat, 10 March 2007 21:26  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
No, its just that the interest level is not there for such a difficult question.
I had a go at this when you posted it, but I ended up with what looked like a Tan distribution.
Your level of mathematics education (or least your ability) is probably a lot greater than most programmers. Since I.T. is a dedicated degree these days, most programmers have not done any mathematics since high school.
Personally, I did two years of mathematics and statistics at university, but that was 20 years ago. I suspect that the problem requires integral calculus plus some other discipline. After doing my best and coming up with something counter to my expectations, I realised very quickly that I did not know enough, and without further research I was wasting my time.
It was an interesting question though.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300105 is a reply to message #300082] |
Thu, 14 February 2008 04:30 |
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
|
Member |
|
|
for example,
if a test table 'T' with a column name 'COL' has data like this:
col
---
1
2
2
3
3
3
4
4
4
4
...
...
...
and you make a query like this:
select col, LPAD ('*', COUNT (*), '*') a
from t
group by col
order by col
then, you can make a distribution f(x)=x
COL A
---------- -------------------
1 *
2 **
3 ***
4 ****
so, likewise if you make some select statement in from clause below,
you can make a 'sine function distribution' of 'column data' with a half period in my first post.
select col, LPAD ('*', COUNT (*), '*') a
from ( ... some select statement ... )
group by col
order by col
if you make some select statement, you can see a sine function distribution with a half period,
SELECT level#
, LPAD ('*', COUNT (*), '*')
FROM ( ... some select statement ... )
GROUP BY level#
LEVEL# LPAD('*',COUNT(*),'*')
------ ----------------------------------------------------
0 *
1 **
2 *******
3 *************
4 *****************
5 **********************
6 **************************
7 ******************************
8 **********************************
9 **************************************
10 ****************************************
11 ********************************************
12 *********************************************
13 ************************************************
14 *************************************************
15 *************************************************
16 **************************************************
17 **************************************************
18 *************************************************
19 ************************************************
20 ***********************************************
21 ********************************************
22 ******************************************
23 ***************************************
24 ***********************************
25 ********************************
26 ****************************
27 ************************
28 *******************
29 **************
30 *********
31 *****
or if you make some select statement, you can see a tangent function distribution with a 1/4 period
0 *
2 *
3 *
4 **
5 ***
6 ***
7 ***
8 *****
9 *****
10 ******
11 ******
12 *******
13 ********
14 **********
15 **********
16 ***********
17 ************
18 **************
19 ****************
20 *****************
21 ********************
22 **********************
23 **************************
24 ******************************
25 *************************************
26 *********************************************
27 ************************************************************
28 **************************************************************************************
can you find that query above?
i hope you understand me exactly.
thanks.
[Updated on: Thu, 14 February 2008 05:40] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #300163 is a reply to message #300137] |
Thu, 14 February 2008 08:11 |
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
|
Member |
|
|
- one or more row source
this row source(row generator) itself is what we should find.
ex. (select ... from dual connect by level ...)
- an output
and this row source(row generator) has sine function distribution.
if you find a row source(row generator) like below,
SELECT TRUNC (1 / 2 + SQRT (2 * LEVEL)) col
FROM DUAL
CONNECT BY LEVEL <= 1000 -- 1000 or whatever number
this row source(row generator) has f(x)=x function distribution.
if you find a row source(row generator) of some kind,
SELECT ...
FROM DUAL
CONNECT BY LEVEL <= 100 -- 100 or whatever number
this row source(row generator) maybe has f(x)=sin(x) function distribution.
so, we should just find that kind of row source(row generator).
this is all.
|
|
|
|
|
|
| Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #300191 is a reply to message #300177] |
Thu, 14 February 2008 09:27 |
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
|
Member |
|
|
if you find a row source like below,
SELECT TRUNC (1 / 2 + SQRT (2 * LEVEL)) col
FROM DUAL
CONNECT BY LEVEL <= 100
it generates:
col
---
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
...
...
...
ie,
1 record with col=1
2 records with col=2
3 records with col=3
4 records with col=4
5 records with col=5
...
...
...
this show a distribution,
col (count of records with asterisks)
--- ---------------------------------
1 *
2 **
3 ***
4 ****
5 *****
6 ******
...
...
...
and THIS MEANS the above row source(row generator) has f(x)=x DISTRIBUTION.
if you find some sort of row source(row generator),
it will generates,
col
---
0
1
1
2
2
2
2
2
2
2
...
...
...
this will show a distribution,
col (count of records with asterisks)
--- ---------------------------------
0 *
1 **
2 *******
3 *************
4 *****************
5 **********************
6 **************************
...
...
...
...
this will have a SIN DISTRIBUTION.
to make a matters more simple,
we can find a combination of built-in functions
(incluing sin function or not is not so important)
in below query.
select /* ..a combination of built-in functions here.. */
from dual
connect by level<=1000 -- 1000 or whatever number
and the above query has a SIN DISTRIBUTION.
|
|
|
|
|
|
|
|
| Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #300629 is a reply to message #300208] |
Sat, 16 February 2008 22:36 |
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
|
Member |
|
|
if you can clearly show the characteristics of sine distribution,
any amplitude will be ok.
but it can only have 1/2 period.
the following is ok:
0 *
1 **
2 *******
3 *************
4 *****************
5 **********************
6 **************************
7 ******************************
8 **********************************
9 **************************************
10 ****************************************
11 ********************************************
12 *********************************************
13 ************************************************
14 *************************************************
15 *************************************************
16 **************************************************
17 **************************************************
18 *************************************************
19 ************************************************
20 ***********************************************
21 ********************************************
22 ******************************************
23 ***************************************
24 ***********************************
25 ********************************
26 ****************************
27 ************************
28 *******************
29 **************
30 *********
31 *****
and the following is also ok!
0 *
1 ***
2 *********
3 **************
4 *******************
5 ***********************
6 ************************
7 *************************
8 ************************
9 *********************
10 ******************
11 ************
12 ******
13 *
|
|
|
|
|
|
| Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #302102 is a reply to message #302098] |
Sat, 23 February 2008 09:33 |
 |
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
After several days I understood the problem. 
But it is more a mathematical/arithmetical problem than a SQL one.
If you are seriously seeking for an answer, you should better post it in a mathematic forum.
Currently I have no time to search on this but I keep it in mind.
If you explain how you came from f(x)=x to v=trunc(1/2+sqrt(2*level)) in your example, maybe this will help us to find a solution for f(x)=K*sin(x).
Regards
Michel
[Updated on: Mon, 06 March 2023 03:13] Report message to a moderator |
|
|
|
| Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #687415 is a reply to message #302102] |
Tue, 07 March 2023 02:44 |
|
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
15 years later, a solution...
SQL> col graph format a60
SQL> with
2 data as (
3 select trunc(1/2+10*acos((level-540)/540)) level#
4 from dual
5 connect by level <= 1080
6 )
7 SELECT level#, LPAD ('*', COUNT (*), '*') graph
8 from data
9 GROUP BY level#
10 ORDER BY level#
11 /
LEVEL# GRAPH
---------- ------------------------------------------------------------
0 *
1 ******
2 **********
3 ****************
4 *********************
5 **************************
6 *******************************
7 **********************************
8 ***************************************
9 ******************************************
10 **********************************************
11 ************************************************
12 **************************************************
13 ****************************************************
14 *****************************************************
15 ******************************************************
16 ******************************************************
17 ******************************************************
18 ****************************************************
19 ***************************************************
20 *************************************************
21 ***********************************************
22 ********************************************
23 ****************************************
24 ************************************
25 *********************************
26 ****************************
27 ***********************
28 ******************
29 *************
30 *******
31 **
32 rows selected. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
