0%

LeetCode第1645题为例。

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
WITH recursive months AS (
SELECT
1 AS month
UNION ALL
SELECT
month + 1
FROM
months
WHERE
month < 12
)
SELECT
m.month AS month,
ifnull(
round(
(
count(
DISTINCT(t.driver_id)
) / (
SELECT
count(driver_id)
FROM
drivers
WHERE
join_date < date_add(
'2020-1-1', INTERVAL m.month month
)
) * 100
),
2
),
0.00
) working_percentage
FROM
(
SELECT
r.ride_id,
r.requested_at requested_date,
ar.ride_id accepted_ride,
ar.driver_id
FROM
rides r
RIGHT JOIN acceptedrides ar ON r.ride_id = ar.ride_id
WHERE
r.requested_at < '2021-01-01'
AND r.requested_at > '2019-12-31'
ORDER BY
r.requested_at
) t
RIGHT JOIN months m ON m.month = month(t.requested_date)
GROUP BY
m.month;
Read more »

留存率

牛客网SQL68

1
2
3
4
5
select
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3) p
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

牛客网SQL70

1
2
3
4
5
6
7
8
9
10
11
12
13
select (select subdate(t2.login_date, 1)) as date,
round(t2.cnt/(select count(user_id) from login where date=(select subdate(t2.login_date, 1)) and (user_id, (select subdate(t2.login_date, 1))) in (select user_id, min(date) as day1 from login group by user_id) group by date), 3) as p from
(select t.login_date, count(t.2ndlogin_diff) cnt from
(select user_id, date as login_date, datediff(nth_value(date, 2) over(partition by user_id order by date),nth_value(date, 1) over(partition by user_id order by date)) as 2ndlogin_diff
from login order by date) t
where t.2ndlogin_diff=1
group by t.login_date
) t2
union
select date, 0.000 p from login where date not in (select min(date) as day1 from login group by user_id)
union
select l.date, 0.000 p from login l where (select date_add(l.date, interval 1 day)) not in (select date from login)
order by date;

OR

Read more »

查看指定端口进程

sudo lsof -i:port

关闭指定端口进程

sudo kill -9 `sudo lsof -t -i:port`

查看本机IP

curl -4 icanhazip.com

Read more »

Laravel学习笔记

部署环境

(使用docker desktop部署)

Laravel sail?

Laravel Sail uses the docker-compose.yml file and the sail script that is stored at the vendor folder of your project at vendor/bin/sail. The sail script provides a CLI with convenient methods for interacting with the Docker containers defined by the docker-compose.yml file.

Issues

80端口占用?

nginx -s stop```或者修改composer.yml的端口设置(改成"8084:80"), 然后重新run ```./vendor/bin/sail up```。然后8084端口访问。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

yaml.scanner.ScannerError: while scanning for the next token
found character '\t' that cannot start any token?
yml文件中不能有tab, 只能空格。

## Routing
- can return:
- view
- json (useful in building API)

for version 8,

```php
use App\Http\Controllers\UserController;

// Using PHP callable syntax...
Route::get('/users', [UserController::class, 'index']);

// Using string syntax...
Route::get('/users', 'App\Http\Controllers\UserController@index');
  • use controller handling routing

Database Accessing

.env and config/database.php

  • php artisan migrate:fresh will drop all migrations and re-run all…
  • php artisan help make:model //generate multifiles..

Views

Forms

Controller

Eloquent

Authentication

Core Concepts

Mail

Notifications

Events

Authorization

Final Project

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
# import datase as dataframe
hang<-read.csv('score.csv', header=TRUE)
lie<-read.csv('TMP_SCORE.csv', header=TRUE)

# dplyr gather and spread
library(dplyr)
library(tidyr)
## spread hang to lie
head(hang)
spreaded<-hang %>%
spread(c_name, grade) %>%
replace(is.na(.), 0) %>%
group_by(stu_id) %>%
summarise(across(everything(), sum)) %>%
select(-id)

head(spreaded)

## gather lie to hang
head(lie)
gathered <- lie %>%
gather("c_name", "grade", -1) %>%
filter(grade!=0) %>%
arrange(stu_id)
head(gathered)