<div id="readability-page-1" class="page"><div>
            <figure><a href="https://leetcode.com/problems/last-person-to-fit-in-the-bus/"><div><p>Last Person to Fit in the Bus - LeetCode</p><p>Can you solve this real interview question? Last Person to Fit in the Bus - Table: Queue +-------------+---------+| Column Name | Type |+-------------+---------+| person_id | int || person_name | varchar || weight | int || turn | int |+-------------+---------+p…</p><p><img src="https://leetcode.com/favicon.ico" alt=""><span>LeetCode</span></p></div><p><img src="https://leetcode.com/static/images/LeetCode_Sharing.png" alt=""></p></a></figure><p>It's a simple problem to calculate cumulative sum. But the question is how to do so in SQL?</p><p>The first idea I've came up with was using <code>AGG() OVER</code> , and I tried <code>OVER(PARTITION BY turn)</code> which was wrong. When I used<code>OVER(ORDER BY turn)</code> , this gives me the correct result.</p><p>The whole code is below:</p><pre><code>SELECT person_name
FROM (
  SELECT person_name, turn, SUM(weight) OVER (ORDER BY turn) AS cum
  FROM Queue
) AS Temp
WHERE CUM &lt;= 1000
ORDER BY cum DESC
LIMIT 1</code></pre><p>Then why was it correct? I asked this to ChatGPT:</p><hr><p>The <code>SUM() OVER()</code> construct in SQL gives a cumulative sum because of the way the <code>OVER</code> clause works.</p><p>When you use <code>SUM() OVER (ORDER BY column)</code>, it calculates the sum in the order specified by the column, and for each row, it includes all rows up to and including that row in the sum. This is why it gives a cumulative sum.</p><p>Here’s a simple example:</p><p>Suppose you have a table <code>sales</code>:</p><table><thead><tr><th>sale_id</th><th>sale_amount</th></tr></thead><tbody><tr><td>1</td><td>100</td></tr><tr><td>2</td><td>200</td></tr><tr><td>3</td><td>300</td></tr></tbody></table><p>If you run the query <code>SELECT sale_id, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_id) as cumulative_sum FROM sales;</code>, you would get:</p><table><thead><tr><th>sale_id</th><th>sale_amount</th><th>cumulative_sum</th></tr></thead><tbody><tr><td>1</td><td>100</td><td>100</td></tr><tr><td>2</td><td>200</td><td>300</td></tr><tr><td>3</td><td>300</td><td>600</td></tr></tbody></table><p>As you can see, for each row, the <code>cumulative_sum</code> column contains the sum of <code>sale_amount</code> for that row and all preceding rows, in the order of <code>sale_id</code>. This is why <code>SUM() OVER (ORDER BY column)</code> gives a cumulative sum.</p><hr><p>In summary, <code>OVER</code> window function can count on preceding rows and a current row. Simple, but powerful! </p>
        </div></div>

일시불 프로그래밍

It's a simple problem to calculate cumulative sum. But the question is how to do so in SQL?
The first idea I've came up with was using AGG() OVER , and I tried OVER(PARTITION BY turn) which was wrong. When I usedOVER(ORDER BY turn) , this gives me the correct result.
The whole code is below:
SELECT person_name
FROM (
  SELECT person_name, turn, SUM(weight) OVER (ORDER BY turn) AS cum
  FROM Queue
) AS Temp
WHERE CUM <= 1000
ORDER BY cum DESC
LIMIT 1
Then why was it correct? I asked this to ChatGPT:
The SUM() OVER() construct in SQL gives a cumulative sum because of the way the OVER clause works.
When you use SUM() OVER (ORDER BY column), it calculates the sum in the order specified by the column, and for each row, it includes all rows up to and including that row in the sum. This is why it gives a cumulative sum.
Here’s a simple example:
Suppose you have a table sales:
sale_idsale_amount
1100
2200
3300
If you run the query SELECT sale_id, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_id) as cumulative_sum FROM sales;, you would get:
sale_idsale_amountcumulative_sum
1100100
2200300
3300600
As you can see, for each row, the cumulative_sum column contains the sum of sale_amount for that row and all preceding rows, in the order of sale_id. This is why SUM() OVER (ORDER BY column) gives a cumulative sum.
In summary, OVER window function can count on preceding rows and a current row. Simple, but powerful!

1204. Last Person to Fit in the Bus

댓글