Shulou(Shulou.com)06/01 Report--

This article mainly explains "classic Java problem analysis and SQL algorithm problem analysis". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn "classic SQL problem and Java algorithm problem analysis".

Description of 1.sql problem

It is said that there is a log table with only two columns, namely continuous id and num. As for what it means, think of it as an amount. Now want to know the number of num for 3 or more times in a row, the data are as follows

Idnum1121314253647484

So it turns out that only 1 sql 4 satisfies the condition. How should I write it?

two。 Thinking and solution

Analysis: the title is simple, there is no ambiguity, can understand, like several times in a row this kind of problem must use the window function, the first thought is the ranking row_number and then lag how to reflect the continuity, it must be the need to use a sort of id, because the title to id is continuously increasing, you can omit the row_number

So the first step, go to lag, and the result is as follows:

Idnumlagid11null210310421531641740840

After getting the lagid, how to use it continuously? first of all, only 0 can meet the conditions, so you can do a filter. The result is as follows: remove the xxx, observe the rows of 0 below, and how to distinguish between 0 of 3 rows and 0 of 7 rows. Think of using the new grouping, rid will sort the lagid and num in the same way, and finally add a column, and the id-rid will be divided into the same group.

Idnumlagidridgid11null xxx

2101131021421 xxx

531 xxx

641 xxx

7401684026-complete sql## solution 1SELECT numFROM (SELECT id, num, lagid, (id-row_number () over (PARTITION BY num, lagid ORDER BY id)) AS gid FROM (SELECT id, num) Num- lag (num) (OVER PARTITION BY 1 ORDER BY id) AS lagid) tmp1 WHERE lagid=0) tmp2GROUP BY num, gidHAVING count (*) > = 2solution 2 select num,gid, count (1) as cfrom (selectid,num,id-row_number () over (PARTITION BY num ORDER BY id) as gidfrom (select * from logs order by num,id) a) bgroup by num,gid

Later, I think of something better. In fact, there is no need for lag or order by global sorting. Like dates, id is generally used to cooperate with row_number to solve continuous problems, so row_number is essential, so you can write it like this (God is fucking simple, don't think about complicated):

SELECT num, gidFROM (SELECT num, id-row_number () OVER (PARTITION BY num ORDER BY id) gidFROM logs) GROUP BY num, gidHAVING count (1) > = 33. Description of Java problem

First, I'll give you an initial array of arr. Then, every day you have to generate a new array based on the previous day's array. The array generated on day I is obtained by doing the following to the array on day I: if an element is smaller than its left and right neighbors, the element increments itself by 1. If an element is larger than its left and right neighbors, the element subtracts 1.

The head and tail elements will never change.

After some time, you will find that the array will no longer change, please return to the resulting array.

Example 1:

Input: [6, 2, 3, 4]

Output: [6, 3, 3, 4]

Explanation:

On the first day, the array changed from [6jin2pd3re4] to [6jcm3pd3re4].

No more operations can be done on the array.

Example 2:

Input: [1, 6, 3, 4, 3, 5]

Output: [1, 4, 4, 4, 4, 5]

Explanation:

On the first day, the array changed from [1 record6, 3, 4, 3, 5] to [1, 5, 4, 4, 4, 5].

The next day, the array changed from [1] to [1] to [1] to [4].

No more operations can be done on the array.

3.3 Analysis and solution

First of all, consider how to write a round of traversal, it should be very simple, the idea is a window with a size of 3.

Use a flag to mark whether the data has been changed in each round. Then the code is as follows:

Public int [] get (int [] input) {if (input = = null | | input.length input [iTun1] & & input [I] > input [I-1]) {input [I]-= 1; if (! flag) flag = true;} while (flag) return input Thank you for your reading, the above is the content of "Classic SQL problem and Java algorithm problem Analysis". After the study of this article, I believe you have a deeper understanding of the problem of classical SQL problem and Java algorithm problem analysis, and the specific use needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.