awk -F: '
/\{|\}|border|max-width|^$/ { print $0 }
!/\{|\}|border|max-width|^$/ {
split( substr($2, 1, length($2)-1) , a," ");
res = "";
for (i in a) {
ind = match(a[i], /[0-9]+px/)
if (ind != 0) {
a[i] = sprintf ("%.2frem", a[i]/10);
}
res=a[i] " " res;
}
res = substr(res, 1, length(res)-1);
print $1 ": " res ";"
}
' some.css
Arrays in awk are different—they are associative. This means that each array is a collection of pairs—an index and its corresponding array element value...
The pairs are shown in jumbled order because their order is irrelevant.
awk -F: '{
if ($0 ~ /\{|\}|border|max-width|^$/ ) { print $0 }
else {
res = "";
split( substr($2, 1, length($2)-1) , a, " ");
for (i=1; i<= length(a);i++) res = res " " ((match(a[i], /[0-9]+px/) != 0)? a[i]/10"rem" : a[i]);
print $1 ": " res";"
}
}' some.css
Пытался сделать через ROW_NUMBERS() OVER (PATRITION BY date ORDER BY date, time, check), ну тут, конечно, получается он игнорит check и просто проставляет 1,2,3,4,5.
Если добавить PATRITION BY date, check, то он, блин, сортирует по check потом и тоже не то выходит
<a>...</a>
<b>...</b>
<b>...</b>
(?<=<a>)(\s*?<b>([^<]+)<\/b>)(\s*?<b>([^<]+)<\/b>)?(\s*?<b>([^<]+)<\/b>)?(\s*?<b>([^<]+)<\/b>)?
(?<=<a>)(\s*?<b>([^<]+)<\/b>){1,}
regex = r"(?<=<a>)(\s*?<b>([^<]+)<\/b>)(\s*?<b>([^<]+)<\/b>)?(\s*?<b>([^<]+)<\/b>)?(\s*?<b>([^<]+)<\/b>)?"
test_str = '''
<a>
<b>test11</b>
</a>
<a>
<b>test21</b>
<b>test22</b>
</a>
<a>
<b>test31</b>
<b>test32</b>
<b>test33</b>
</a>
<a>
<b>test41</b>
<b>test42</b>
<b>test43</b>
<b>test44</b>
</a>
<c>
<b>test51</b>
<b>test52</b>
</c>
'''
matches = re.finditer(regex, test_str, re.MULTILINE)
for matchNum, match in enumerate(matches, start=1):
for groupNum in range(0, len(match.groups())):
groupNum = groupNum + 1
if (groupNum % 2 == 0) & (match.group(groupNum) != None):
print (match.group(groupNum) )
SELECT
id,
dt,
CASE DATE(dt)
WHEN CURDATE() THEN 'Сегодня'
WHEN CURDATE() + INTERVAL -1 DAY THEN 'Вчера'
ELSE 'Ранее'
END AS when_day
FROM items
;
$now = new DateTime(); // текущая дата
switch ($DATEPOSTED) { // в формате Y m d
case $now->format("Y m d"):
echo "сегодня";
break;
case ((clone $now)->modify("yesterday"))->format('Y m d'):
echo "вчера";
break;
default:
echo "ранее";
}
$DATEPOSTED = strtotime("-1 day");
$DATEPOSTED = date('Y m d', strtotime(' -1 day'));
if ($now == $DATEPOSTED) {
tuples = ((123, "foo"), (42, "bar"), (23, "baz"))
cur.executemany("INSERT INTO test (num, data) VALUES (%s, %s)", tuples)
SELECT json_build_object(
'account_id', id,
'first_name', first_name,
'last_name', last_name
) AS author
FROM accounts
json_build_object(
'post_id', p.id,
'body', p.body,
'author', json_build_object(
'account_id', a.id,
'first_name', a.first_name,
'last_name', a.last_name
)
)
при объединении таблиц posts и accountsSELECT json_agg(json_build_object(
'post_id', p.id,
'body', p.body,
'author', json_build_object(
'account_id', a.id,
'first_name', a.first_name,
'last_name', a.last_name
)
)) AS posts
FROM posts p
JOIN accounts a ON a.id = p.author_id
SELECT json_agg(json_build_object(
'comment_id', c.id,
'body', c.body,
'author', json_build_object(
'account_id', a.id,
'first_name', a.first_name,
'last_name', a.last_name
)
)) AS comments
FROM comments c
JOIN accounts a ON a.id = c.author_id
GROUP BY post_id
WITH cmts AS (
SELECT json_agg(json_build_object(
'comment_id', c.id,
'body', c.body,
'author', json_build_object(
'account_id', a.id,
'first_name', a.first_name,
'last_name', a.last_name
)
)) AS comments,
post_id
FROM comments c
JOIN accounts a ON a.id = c.author_id
GROUP BY post_id
)
SELECT json_agg(json_build_object(
'post_id', p.id,
'body', p.body,
'author', json_build_object(
'account_id', a.id,
'first_name', a.first_name,
'last_name', a.last_name
),
'comments', COALESCE(c.comments, '[]')
)) AS "posts with comments"
FROM posts p
JOIN accounts a ON a.id = p.author_id
LEFT JOIN cmts c ON c.post_id = p.id
-- вывод ветки дерева
WITH RECURSIVE
cte (id, title, parent_id) AS (
SELECT id,
title,
parent_id
FROM test
WHERE id = 1 -- < id узла от которого выводить
UNION ALL
SELECT t.id,
t.title,
t.parent_id
FROM test t
INNER JOIN cte
ON t.parent_id = cte.id
)
SELECT * FROM cte;
ON t.parent_id = cte.id
т.е. ON t.id = cte.parent_id
и из полученного выбрать запись с parent_id is null-- вывод самого верхнего родителя по дочернему узлу
WITH RECURSIVE
cte (id, title, parent_id) AS (
SELECT id,
title,
parent_id
FROM test
WHERE id = 4 -- < id узла
UNION ALL
SELECT t.id,
t.title,
t.parent_id
FROM test t
INNER JOIN cte
ON t.id = cte.parent_id
)
SELECT * FROM cte
WHERE parent_id IS NULL;
SELECT * FROM (
SELECT id,
title,
parent_id
FROM (SELECT * FROM test ORDER BY id DESC) test_sorted
JOIN (select @pv := 4) initialisation -- < id узла
WHERE find_in_set(id, @pv)
AND length(@pv := concat(@pv, ',', COALESCE(parent_id, '')))
) t
WHERE parent_id is null
population ~ '^[0-9]+$'
-- Выбираем по population где только число и больше 4000
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND population ~ '^[0-9]+$'
AND population::int > 4000
-- Отбираем нечисловые значение в population и вставляем в таблицу contr_planet_osm_point
INSERT INTO contr_planet_osm_point
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND population !~ '^[0-9]+$'
SELECT *
FROM (
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND population ~ '^[0-9]+$'
) t
WHERE population::int > 4000
WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND CASE WHEN population ~ '^[0-9]+$' THEN population::int > 4000 ELSE false END
;
WITH f AS (
SELECT 'PARAM1 PARAM3 PARAM7' as findstr
),
fr AS (
SELECT t.value AS param
FROM f
CROSS APPLY STRING_SPLIT(f.findstr, ' ') t
)
SELECT d.id, count(1) AS num_matches
FROM data d
CROSS JOIN fr
WHERE charindex(fr.param, d.parameters) > 0
GROUP BY id
HAVING count(1) >= 2
;
WITH f AS (
SELECT 'PARAM1 PARAM3 PARAM7' as findstr
)
SELECT d.id, count(1) AS num_matches
FROM data d
CROSS APPLY STRING_SPLIT(d.parameters, ' ') ds
JOIN f ON 1=1
WHERE charindex(ds.value, f.findstr) > 0
GROUP BY d.id
HAVING count(1) >= 2
;
([^= ]+)="(.*?)"\s+(?=([^= ]+=|\/>))
$str = '
<order acode="7102774" ProductName="Какой-то текст "с скобочками", которые нужно заменть" ClientBarCode="" />
<order acode="7102774" ProductName="Какой-то текст "с скобочками", которые нужно заменть" ClientBarCode="" />
<order acode="7102774" ProductName="Какой-то текст "с скобочками", которые нужно заменть" ClientBarCode="" />
<order acode="7102774" ProductName="Какой-то текст "с скобочками", которые нужно заменть" ClientBarCode="" />';
$out = preg_replace_callback(
'/([^= ]+)="(.*?)"\s+(?=([^= ]+=|\/>))/',
function($m) {
return $m[1].'="'.htmlspecialchars($m[2], ENT_QUOTES).'" ';
},
$str);
echo $out;
SELECT arr[5] AS url, arr[8] AS user_agent
FROM some_table AS st
CROSS JOIN LATERAL STRING_TO_ARRAY(st.data, chr(9)) AS arr
SELECT
split_part(data, chr(9), 5),
split_part(data, chr(9), 8)
FROM some_table
SELECT substring(data,'http[^\t]+') AS url, substring(data,'[^\t]+$') AS user_agent FROM some_table
SELECT
region, browser, cnt
FROM (
SELECT
t.* , ROW_NUMBER() OVER (PARTITION BY region ORDER BY cnt DESC) n
FROM (
SELECT region, browser, count(1) cnt
FROM brostat
GROUP BY region, browser
) t
) tt WHERE n = 1
;
SELECT
region, browser, count(1) cnt
FROM brostat bs
GROUP BY region, browser
HAVING count(1) = (
SELECT MAX(cnt)
FROM (
SELECT
region, browser, count(1) cnt
FROM brostat
GROUP BY region, browser
) t
WHERE t.region = bs.region
GROUP BY region
)
;
WITH param AS (
SELECT 5 AS prod_id -- ИД продукта которому ищем сопутствующие
)
SELECT p2.prod_id
FROM param, prod_ch_val p1, prod_ch_val p2
WHERE p1.prod_id = param.prod_id -- ИД продукта
AND p1.ch_id=p2.ch_id
AND p1.val = p2.val
AND p1.prod_id != p2.prod_id
AND (SELECT count(1) FROM prod_ch_val t WHERE t.prod_id=p2.prod_id) = (SELECT count(1) FROM prod_ch_val t WHERE t.prod_id=p1.prod_id)
GROUP BY p2.prod_id
sed -r '
s/(className="[^"]+)([A-Z])([^"]+)([A-Z])([^"]+)([A-Z])([^"]+")/\1-\L\2\3-\L\4\5-\L\6\7/g;
s/(className="[^"]+)([A-Z])([^"]+)([A-Z])([^"]+")/\1-\L\2\3-\L\4\5/g;
s/(className="[^"]+)([A-Z])([^"]+")/\1-\L\2\3/g
' input_file > output_file
sed -i -r '
s/(className="[^"]+)([A-Z])([^"]+)([A-Z])([^"]+)([A-Z])([^"]+")/\1-\L\2\3-\L\4\5-\L\6\7/g;
s/(className="[^"]+)([A-Z])([^"]+)([A-Z])([^"]+")/\1-\L\2\3-\L\4\5/g;
s/(className="[^"]+)([A-Z])([^"]+")/\1-\L\2\3/g
' *.jsx
sed -r ':a;N;$!ba;s/([^;][^\?])[\r\n]+/\1/g' source_file > target_file
sqlite3.exe my_example_db.sqlt
create table ru_word (word varchar(100), freq INT);
.separator " "
.import ru_50k.txt ru_word
: переходим в папку с БД, там же должен быть файлru_50k.txt
cd C:\tmp
: открываем БД в sqlite3
c:\bin\sqlite3.exe my_example_db.sqlt
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
sqlite> create table ru_word (word varchar(100), freq INT);
sqlite> .tables
ru_word
sqlite> .separator " "
sqlite> .import ru_50k.txt ru_word
sqlite> SELECT count(*) FROM ru_word;
50000
sqlite> SELECT MAX(freq) FROM ru_word;
3854593
sqlite> SELECT * FROM ru_word LIMIT 10;
я 3854593
не 3244771
что 2665565
в 2212420
и 2179689
ты 2067418
это 1999607
на 1240437
с 1128276
он 966203
sqlite> .quit