Nesterovsky bros - AI
http://www.nesterovsky-bros.com/weblog/
en-usNesterovsky brosMon, 01 Jan 2024 14:02:01 GMTnewtelligence dasBlog 2.3.12105.0contact@nesterovsky-bros.comcontact@nesterovsky-bros.comhttp://www.nesterovsky-bros.com/weblog/Trackback.aspx?guid=0977bf46-abc6-41fe-9767-de673ef5de8bhttp://www.nesterovsky-bros.com/weblog/pingback.aspxhttp://www.nesterovsky-bros.com/weblog/PermaLink,guid,0977bf46-abc6-41fe-9767-de673ef5de8b.aspxVladimir Nesterovsky0 0http://www.nesterovsky-bros.com/weblog/SyndicationService.asmx/GetEntryCommentsRss?guid=0977bf46-abc6-41fe-9767-de673ef5de8b
As we wrote some time ago, we started to use AI and ML APIs in our applications, but
this time we've tried to use Bing chat as a tool.
The initial task was to collect sequences of integer numbers and return a list of
pairs integers where the first number is a range's start and the second one is a count
of values in the range. For example, for sequence 1, 2, 3, 5, 8, 9 we'd like to get
the following results: 1, 3 5, 1 8, 2
We both started to solve this issue. My brother started to play with SSMS using his
deep knowledges in SQL and analytic functions, whereas me started to play with Bing
chat. Looking ahead, I can state that we both solved the issue using almost the same
SQL statement almost in the same time.
Here are my prompts and Bing's answers:
Prompt:
Act as database analyst and software engineer. Given the table (T) with a numeric
field (N), which values are orderd ranges of integers. In a range each next value
is greater than previous one on 1. When value differs from the previous one more than
on 1 then in belongs to another range. Generate select statement that returns rows
with two fields. The first field (R) is a number from which a range starts and the
second field (C) is count elements of that range:
Answer:
Sure, I can help with that. Here's a SQL query that should do the trick. This query
uses a self-join to identify the start of each range, and then groups by the start
of the range to count the number of elements. ```sql
SELECT start_of_range AS R, COUNT(*) AS C FROM ( SELECT N, N - ROW_NUMBER() OVER (ORDER
BY N) AS start_of_range FROM T ) subquery GROUP BY start_of_range ORDER BY start_of_range;
``` In this query, `start_of_range` is calculated as the difference between
the current value of `N` and its row number (when ordered by `N`). This difference
will be the same for all `N` in the same range. Then we group by `start_of_range`
to get the count of elements in each range. The outer query selects the start of each
range (`R`) and the count of elements in that range (`C`).
Me, scratching the head, hmmm...
Question:
This is a bit incorrect SQL select. For example for values 1, 2, 3, 5, 8, 9 I'd like
to get the following results: 1, 3 5, 1 8, 2 What a SQL select shall I use to get
such results?
Answer:
I apologize for the confusion earlier. You're right, the previous query doesn't handle
the scenario you've described correctly. Here's a corrected SQL query that should
give you the desired output. This query uses a window function to compare each row
with the previous one and identify the start of a new range. Then it groups by the
start of the range to count the elements. ```sql
WITH ranges AS ( SELECT N, CASE WHEN N - LAG(N, 1, N) OVER (ORDER BY N) > 1 THEN 1
ELSE 0 END AS is_start_of_range FROM T ), range_groups AS ( SELECT N, SUM(is_start_of_range)
OVER (ORDER BY N) AS range_group FROM ranges ) SELECT MIN(N) AS R, COUNT(*) AS C FROM
range_groups GROUP BY range_group ORDER BY R; ``` In this query, `is_start_of_range`
is a flag that indicates the start of a new range (when the difference between the
current and previous `N` is more than 1). `range_group` is a running total of `is_start_of_range`
that serves as a unique identifier for each range. The outer query then groups by
`range_group` to get the start (`R`) and count (`C`) of each range. I hope this helps!
Let me know if you have any other questions.
That answer more satisfied me, and I've checked the following example:
with T as ( select 10 N union
all select 11 N union all select 30 N union all select 31 N union all select 32 N
union all select 50 N union all select 80 N union all select 81 N union all select
82 N union all select 83 N union all select 90 N ), ranges AS ( SELECT N, CASE WHEN
N - LAG(N, 1, N) OVER (ORDER BY N) > 1 THEN 1 ELSE 0 END AS is_start_of_range FROM
T ), range_groups AS ( SELECT N, SUM(is_start_of_range) OVER (ORDER BY N) AS range_group
FROM ranges ) SELECT MIN(N) AS R, COUNT(*) AS C FROM range_groups GROUP BY range_group
ORDER BY R;
The result was:
R C ------------ 10 2 30
3 50 1 80 4 90 1
Conclusion:
Nowdays, using AI as a tool, you may improve your performance to the expert level
and, may be, study something new...
P.S. Don't fool youself that AI can replace a developer (see the first answer
of the chat), but together they can be much stornger than separatelly.
P.P.S. Another interesting solution gave us our colleague. She used an additional
field to avoid using analytics function, and she reached the same result:
with T as ( select 10 ID,
1 N union all select 11 ID, 1 N union all select 30 ID, 1 N union all select 31 ID,
1 N union all select 32 ID, 1 N union all select 50 ID, 1 N union all select 80 ID,
1 N union all select 81 ID, 1 N union all select 82 ID, 1 N union all select 83 ID,
1 N union all select 90 ID, 1 N ), Groups AS ( SELECT ID, N, ROW_NUMBER() OVER (ORDER
BY ID) - ID AS GroupNumber FROM T ) SELECT MIN(ID) AS R, SUM(N) AS C FROM Groups GROUP
BY GroupNumber ORDER BY StartID; Using Bing chat as a tool.http://www.nesterovsky-bros.com/weblog/PermaLink,guid,0977bf46-abc6-41fe-9767-de673ef5de8b.aspx
http://www.nesterovsky-bros.com/weblog/2024/01/01/UsingBingChatAsATool.aspx
Mon, 01 Jan 2024 14:02:01 GMT <p>
As we wrote some time ago, we started to use AI and ML APIs in our applications, but
this time we've tried to use Bing chat as a tool.
</p>
<p>
The initial task was to collect sequences of integer numbers and return a list of
pairs integers where the first number is a range's start and the second one is a count
of values in the range. For example, for sequence 1, 2, 3, 5, 8, 9 we'd like to get
the following results: 1, 3 5, 1 8, 2
</p>
<p>
We both started to solve this issue. My brother started to play with SSMS using his
deep knowledges in SQL and analytic functions, whereas me started to play with Bing
chat. Looking ahead, I can state that we both solved the issue using almost the same
SQL statement almost in the same time.
</p>
<p>
Here are my prompts and Bing's answers:
</p>
<dl>
<dt style="font-weight: bold;">Prompt:</dt>
<dd>
Act as database analyst and software engineer. Given the table (T) with a numeric
field (N), which values are orderd ranges of integers. In a range each next value
is greater than previous one on 1. When value differs from the previous one more than
on 1 then in belongs to another range. Generate select statement that returns rows
with two fields. The first field (R) is a number from which a range starts and the
second field (C) is count elements of that range:
</dd>
<dt style="font-weight: bold;">Answer:</dt>
<dd style="white-space: normal;">
Sure, I can help with that. Here's a SQL query that should do the trick. This query
uses a self-join to identify the start of each range, and then groups by the start
of the range to count the number of elements. <code style="white-space: pre; margin: 1em; font-family: monospace;"> ```sql
SELECT start_of_range AS R, COUNT(*) AS C FROM ( SELECT N, N - ROW_NUMBER() OVER (ORDER
BY N) AS start_of_range FROM T ) subquery GROUP BY start_of_range ORDER BY start_of_range;
``` </code> In this query, `start_of_range` is calculated as the difference between
the current value of `N` and its row number (when ordered by `N`). This difference
will be the same for all `N` in the same range. Then we group by `start_of_range`
to get the count of elements in each range. The outer query selects the start of each
range (`R`) and the count of elements in that range (`C`).
</dd>
<dt style="padding: 1em; font-weight: 400;"> Me, scratching the head, hmmm... </dt> <dt style="font-weight: bold;"> Question: </dt>
<dd style="white-space: pre;">
This is a bit incorrect SQL select. For example for values 1, 2, 3, 5, 8, 9 I'd like
to get the following results: 1, 3 5, 1 8, 2 What a SQL select shall I use to get
such results?
</dd>
<dt style="font-weight: bold;">Answer:</dt>
<dd>
I apologize for the confusion earlier. You're right, the previous query doesn't handle
the scenario you've described correctly. Here's a corrected SQL query that should
give you the desired output. This query uses a window function to compare each row
with the previous one and identify the start of a new range. Then it groups by the
start of the range to count the elements. <code style="white-space: pre;font-family: monospace;"> ```sql
WITH ranges AS ( SELECT N, CASE WHEN N - LAG(N, 1, N) OVER (ORDER BY N) > 1 THEN 1
ELSE 0 END AS is_start_of_range FROM T ), range_groups AS ( SELECT N, SUM(is_start_of_range)
OVER (ORDER BY N) AS range_group FROM ranges ) SELECT MIN(N) AS R, COUNT(*) AS C FROM
range_groups GROUP BY range_group ORDER BY R; ``` </code> In this query, `is_start_of_range`
is a flag that indicates the start of a new range (when the difference between the
current and previous `N` is more than 1). `range_group` is a running total of `is_start_of_range`
that serves as a unique identifier for each range. The outer query then groups by
`range_group` to get the start (`R`) and count (`C`) of each range. I hope this helps!
Let me know if you have any other questions.
</dd>
</dl>
<p>
That answer more satisfied me, and I've checked the following example:
</p>
<code style="white-space: pre; font-family: monospace;"> with T as ( select 10 N union
all select 11 N union all select 30 N union all select 31 N union all select 32 N
union all select 50 N union all select 80 N union all select 81 N union all select
82 N union all select 83 N union all select 90 N ), ranges AS ( SELECT N, CASE WHEN
N - LAG(N, 1, N) OVER (ORDER BY N) > 1 THEN 1 ELSE 0 END AS is_start_of_range FROM
T ), range_groups AS ( SELECT N, SUM(is_start_of_range) OVER (ORDER BY N) AS range_group
FROM ranges ) SELECT MIN(N) AS R, COUNT(*) AS C FROM range_groups GROUP BY range_group
ORDER BY R; </code>
<p>
The result was:
</p>
<code style="white-space: pre; font-family: monospace;"> R C ------------ 10 2 30
3 50 1 80 4 90 1 </code>
<p>
<b>Conclusion:</b>
<br />
Nowdays, using AI as a tool, you may improve your performance to the expert level
and, may be, study something new...
</p>
<p>
<b>P.S.</b> Don't fool youself that AI can replace a developer (see the first answer
of the chat), but together they can be much stornger than separatelly.
</p>
<p>
<b>P.P.S.</b> Another interesting solution gave us our colleague. She used an additional
field to avoid using analytics function, and she reached the same result:
</p>
<code style="white-space: pre; font-family: monospace;"> with T as ( select 10 ID,
1 N union all select 11 ID, 1 N union all select 30 ID, 1 N union all select 31 ID,
1 N union all select 32 ID, 1 N union all select 50 ID, 1 N union all select 80 ID,
1 N union all select 81 ID, 1 N union all select 82 ID, 1 N union all select 83 ID,
1 N union all select 90 ID, 1 N ), Groups AS ( SELECT ID, N, ROW_NUMBER() OVER (ORDER
BY ID) - ID AS GroupNumber FROM T ) SELECT MIN(ID) AS R, SUM(N) AS C FROM Groups GROUP
BY GroupNumber ORDER BY StartID; </code><img width="0" height="0" src="http://www.nesterovsky-bros.com/weblog/aggbug.ashx?id=0977bf46-abc6-41fe-9767-de673ef5de8b" />http://www.nesterovsky-bros.com/weblog/CommentView,guid,0977bf46-abc6-41fe-9767-de673ef5de8b.aspxAISQL Server puzzleThinking aloudTips and tricks