Home » Other » General » Puzzle n°09 - Finding a query for sine function distribution **
Puzzle n°09 - Finding a query for sine function distribution ** Sun, 06 August 2006 23:35
 zozogirl Messages: 77Registered: 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 #223740 is a reply to message #186198] Sat, 10 March 2007 19:32
 zozogirl Messages: 77Registered: November 2005 Location: Seoul, Korea Member
Is my quiz mathematically incorrect?
Or is it weird?
Or is my english not goot?
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: 3728Registered: 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 #300056 is a reply to message #186198] Thu, 14 February 2008 02:12
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Senior Member
One more interesting puzzle.

Enjoy.

Rajuvan.

[Updated on: Thu, 14 February 2008 02:42] by Moderator

Report message to a moderator

Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300065 is a reply to message #300056] Thu, 14 February 2008 02:42
 Michel Cadot Messages: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
It's just a row generator and applying the function you want.
```SQL> col val format a80
SQL> col line format 990
SQL> with lines as (select level-1 line from dual connect by level <= 100)
3  from lines
4  order by line
5  /
LINE VAL
---- --------------------------------------------------------------------------------
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 *****************************************************
33 **************************************************
34 **********************************************
35 *******************************************
36 ***************************************
37 ************************************
38 *********************************
39 *****************************
40 **************************
41 ***********************
42 *******************
43 *****************
44 **************
45 ***********
46 *********
47 *******
48 *****
49 ***
50 **
51 *
52
53
54
55
56
57 *
58 **
59 ***
60 *****
61 *******
62 *********
63 ***********
64 **************
65 *****************
66 ********************
67 ***********************
68 **************************
69 *****************************
70 *********************************
71 ************************************
72 ****************************************
73 *******************************************
74 **********************************************
75 **************************************************
76 *****************************************************
77 ********************************************************
78 ************************************************************
79 **************************************************************
80 *****************************************************************
81 ********************************************************************
82 **********************************************************************
83 ************************************************************************
84 **************************************************************************
85 ****************************************************************************
86 *****************************************************************************
87 ******************************************************************************
88 *******************************************************************************
89 *******************************************************************************
90 *******************************************************************************
91 *******************************************************************************
92 *******************************************************************************
93 ******************************************************************************
94 *****************************************************************************
95 ****************************************************************************
96 **************************************************************************
97 ************************************************************************
98 **********************************************************************
99 ********************************************************************

100 rows selected.```

Regards
Michel

Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300069 is a reply to message #300065] Thu, 14 February 2008 02:57
 zozogirl Messages: 77Registered: November 2005 Location: Seoul, Korea Member
but i don't want just a sine function.
i want a 'distribution function' of column values.
thanks.

Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300076 is a reply to message #300069] Thu, 14 February 2008 03:19
 Frank Messages: 7901Registered: March 2000 Senior Member
Nice solution (once again!)
by the way: pi is 2 * acos(0)
Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300077 is a reply to message #300076] Thu, 14 February 2008 03:21
 zozogirl Messages: 77Registered: November 2005 Location: Seoul, Korea Member
Frank, this is not what i want.

Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300079 is a reply to message #186198] Thu, 14 February 2008 03:25
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Senior Member
Ie, OP wants Something like Wrapper Function with some input , and display like Full single curve structure.

ie, If input is 50 . OP wants the Full curve structure with 50 lines. and so on ..

So Let me update the star level

Rajuvan

[Updated on: Thu, 14 February 2008 03:32]

Report message to a moderator

Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300082 is a reply to message #300069] Thu, 14 February 2008 03:29
 Michel Cadot Messages: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
I don't understand what you want.
Could you post a full test case.

Regards
Michel
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: 77Registered: 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 #300108 is a reply to message #300105] Thu, 14 February 2008 04:44
 Michel Cadot Messages: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Sorry, but I still don't understand.
I don't understand what you have and what you want.
Assume you work with scott.emp table and sal column, what do you want from it?

Regards
Michel
Re: Puzzle n°09 - Finding a query for sine function distribution * [message #300112 is a reply to message #300108] Thu, 14 February 2008 04:54
 zozogirl Messages: 77Registered: November 2005 Location: Seoul, Korea Member
if you have a query like this:

```SELECT  TRUNC (1 / 2 + SQRT (2 * LEVEL)) col
FROM    DUAL
CONNECT BY LEVEL <= 1000
```

data of column 'col' have a 'sine function distribution'.
you can under stand me?

[Updated on: Thu, 14 February 2008 05:41] by Moderator

Report message to a moderator

Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #300129 is a reply to message #186198] Thu, 14 February 2008 05:37
 rajavu1 Messages: 1574Registered: May 2005 Location: Bangalore , India Senior Member
Hi Micheal,

Posting looks difficult to understand .

What I understood as per zozo's description is

1. She wants the output from One Outer and Inner query .

2. Inner query should be different for Sine distribution for 1/2 period and Tan distribution of 1/4 period .

3. Outer query (used to display Graphic distribution) should be same

4. Sine function distribution with a half period is

```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 *****
```

5. Tan function distribution with a 1/4 period is

```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 **************************************************************************************```

6. Inner select query should be from some Row generator . it is nothing to do with Scott.Emp.

Rajuvan.
Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #300131 is a reply to message #300129] Thu, 14 February 2008 05:41
 zozogirl Messages: 77Registered: November 2005 Location: Seoul, Korea Member
yes, what rajavu1 says is exactly what i want!
thank you again, rajavu1

Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #300137 is a reply to message #300131] Thu, 14 February 2008 06:11
 Michel Cadot Messages: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
I hope you will find the solution as I still don't understand.
For a SQL query has:
- one or more row source
- an output

I understand neither the first part nor the last one in your post.
But don't care, it is not mandatory I understand, better spend your time to find the solution.

Regards
Michel
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: 77Registered: 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 #300177 is a reply to message #300163] Thu, 14 February 2008 08:52
 Michel Cadot Messages: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
If the question is:
- row source whatever you want
- output sin "distribution"

Then my query generates that, doesn't it?

Ah! but it must generates that without SIN function, is this the question?

Regards
Michel
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: 77Registered: 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 #300205 is a reply to message #300191] Thu, 14 February 2008 10:41
 Michel Cadot Messages: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
But is this not what my query gives?

Regards
Michel
Re: Puzzle n°09 - Finding a query for sine function distribution ** [message #300208 is a reply to message #300205] Thu, 14 February 2008 10:47
 Michel Cadot Messages: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Ah OK! I understand (finally) but what is the meaning of a sin distribution?
The number of rows having value x is sin(x).
But sin(x) is between -1 and 1.

Regards
Michel

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: 77Registered: 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 #302098 is a reply to message #300629] Sat, 23 February 2008 08:05
 zozogirl Messages: 77Registered: November 2005 Location: Seoul, Korea Member
can anybody understand and solve this quiz?

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: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: 68597Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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.```
 Previous Topic: Puzzle n°03 - Sharing articles *** Next Topic: Puzzle n°04 - Evenly share batches of articles into groups ***
Goto Forum:

Current Time: Sat Feb 24 06:31:55 CST 2024