select l.*
,(select new_status
from change_log ch
where ch.id=l.id
and ch.date<=l.date_message
order by ch.date desc
limit 1) as state
from List lselect l.*
,(select new_status
from change_log ch
where ch.id=l.id
and ch.date=(select max(ch.date)
from change_log ch
where ch.date<=l.date_message)
) as state
from List lwith a as #преобоазуем в json массив
(select id,concat('[',replace(descr,'}{','},{'),']') as d1
from x1
)
,b as ( # массив в таблицу
select id,JSON_EXTRACT(d1, '$[1]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[2]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[3]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[4]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[5]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[6]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[7]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[8]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[9]') as d2
from a
union all
select id,JSON_EXTRACT(d1, '$[10]') as d2
from a
)
-- подсчет
select id,count(distinct JSON_VALUE(d2,'$.DeviceID')) as count
from b
where b.d2 is not null
group by idconst { chromium } = require('playwright');
const { google } = require('googleapis');
async function run {
try{
//Парсим данные
const browser = await chromium.launch({
headless: true
});
const context = await browser.newContext();
const page = await context.newPage();
const sURL="https://quote.eastmoney.com/qihuo/OI409.html"
await page.goto(sURL);
await page.waitForTimeout(3000); //почему то на стандартном waitForLoad после 2-х раз не дало загрузить
var data=await page.locator("div.zxj").innerText(); // наша цифра тут
console.log(data); //контрольный вывод
await context.close();
await browser.close();
//Записываем в таблицу
const auth = new google.auth.GoogleAuth({
keyFile: 'token.json',
scopes: ['https://www.googleapis.com/auth/spreadsheets']
});
//-----------------------------------------------------------------
const sheets = google.sheets({ version: 'v4', auth });
var spreadsheetId="spreadsheetId"; //id табицы
var range="Sheet1!A1:A1" //лист и диапазон
var values = [];
values[0]=data;
sheets.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption: 'RAW',
resource: {
values
}
}, (err, res) => {
if (err) return console.log(`The API returned an error: ${err}`);
console.log(res.data);
});
} catch (error) {
console.log(error);
}
}
run();