postgresql – Share a single index for several columns with linear correlation

I have a table with two columns whose values ​​have perfect linear correlation, for example

CREATE TABLE measurements (
  sensor int PRIMARY KEY,
  num serial PRIMARY KEY,
  time timestamptz DEFAULT now(),
  value float
);
-- many times:
INSERT INTO measurements(sensor, value) VALUES ($1, $2);

Both time and the num are increasing monotonously, a row with more num the value will also have greater time value.

Postgres will create a btree index on the primary key columns. Can i say it too use the same index when searching for lines by their time instead of their num? A sin

SELECT * FROM measurements WHERE sensor = $1 AND time >= $2 ORDER BY time;

The resulting lines would have exactly the same order as if they were sorted by num.

Is there a way to let the optimizer know? I've seen many articles on column correlation statistics, most of them being linked in this StackOverflow topic, but multi-column statistics seem to only analyze dependencies between individual values ​​and cannot not make a linear correlation.

I was hoping to get the same result as if I created another index on sensor, time, but postgres only need to keep and store one index.

powershell – List all columns 2 for each row in column 1 that matches

Scenario: I have a CSV with around 500 rows, column A (site) has 75 unique values; Column B (radius) contains 60 unique values
I have to fill ComboBox1 with only unique values ​​from column A. Then, according to the selection of ComboBox1, I have to fill ComboBox2 with all the values ​​of ColumnB which are associated with the selection ComboBox1.

Example of CSV
Site, Department
S01, AA1
S01, FF6
S01, HHH
S02, MM5
S02, PP4
S02, QQ0
S02, RRR
S03, UU3
S03, VV3
S03, YY2

If my ComboBox1 = S01, ComboBox2 must fill with AA1, FF6, HHH
If my ComboBox1 = S02, ComboBox2 must fill with MM5, PP4, QQ0 RRR
etc …

I import my CSV into a table

$MySites=@()
$MyDepts=@()

I need to fill ComboBox1 with only the unique rows in column A

$ComboBox1 = Import-CSV .myTest.csv | ForEach-Object {$MySites += $_.site | Where-Object $AllSites.site.count -eq 1 }

Once the user has made a selection in ComboBox1, ComboBox2 should be filled with only rows from ColumnB that match the selection in ComboBox1

$ComboBox2 = Import-CSV .myTest.csv | ForEach-Object {$MyDepts += $_.Dept | Where-Object $_.site -eq $ComboBox1 }

It doesn't work … I've searched and read and searched more but I can't find a solution

Microsoft Excel – How do I get vlookup to return multiple columns in a cell?

I prepare a list of items bought by the buyer and try to list it in a cell. I tried to do this using vlookup and textjoin but I can't seem to display information. I want to take a customer name on a sheet and then display all the references purchased in a cell.


= TEXTJOIN ("", 1, VLOOKUP (A12, Display_sales_named, {3,4,5,6,7,8,9,10,11,12,13,14,15,16}, 0))

= VLOOKUP (A3, Display_sales_named, TEXTJOIN ("", 1, {3,4,5,6,7,8,9,10,11,12,13,14,15,16}), 0)


The image is the table from which I draw and I try to display the information in a table on another sheet.
enter description of image here

sql server – converting json to columns

I have the following json structure

{ "products":  (

{
  "description": "Horse shoes",
  "productid": 1,
  "orders": (
    { "customerid": 101 },
    { "customerid": 102 },
    { "customerid": 103 }
  )
},
{
  "description": "Horse food",
  "productid": 2,
  "orders": (
    { "customerid": 104 },
    { "customerid": 105 },
    { "customerid": 106 }
  )
}  )}

How can i convert this to lines using something like

select * from myTable cross apply openJson(myJsonColumn)…

And get a result like this:

rowId|productId|Description|CustomerIdHasOrdered
1000|1|Hourse shoes"|101
1000|1|Hourse shoes"|102
1000|1|Hourse shoes"|103
1001|2|Hourse food"|104
1001|2|Hourse food"|105
1001|2|Hourse food"|106

These are just a few examples of simple data and not a real way to store this type of information;)

list – SharePoint Online – Formatting JSON columns

I have a search list in my form. This gives the user the ability to choose multiple unique values.

The problem I am having is that the values ​​are being translated in the SharePoint list with [ID] "; #" [single value]

Here is an example of what I see in the SharePoint list. I would like to format the column with JSON to remove the [ID] and "https://sharepoint.stackexchange.com/#".

Sample list:
1275; #Family information; # 1245; #Training and skills

Example of what I would like to see:
Family information; Education and skills

Thank you!

Finding the CIM PowerShell equivalent of Sql Server columns sys.dm_os_sys_info

I have a normal SQL server that I select and which collects information from these two columns from a dynamic management view:

SELECT cpu_count, hyperthread_ratio FROM sys.dm_os_sys_info

I wonder if anyone knows the precise equivalent via a PowerShell CIM system hardware class? Something like:

Get-CimInstance -ClassName Win32_Processor

But I don't know PC architecture enough to know which property (or logic based on other properties) of that directly equates to cpu_count and hyperthread_ratio of that sql dmv. Thank you.

mysql 5.6 – How to extract the desired results from different columns

In order to conserve space and eliminate duplicates, I have the following table of distances between locations:
| id | patientIDa | patientIDb | distance |
| —- | ———— | ———— | ———- |
| 1 | 1 | 2 | 4.5 |
| 2 | 1 | 3 | 2.8 |
| 3 | 1 | 4 | 1.3 |
| 4 | 1 | 5 | 0.9 |
| 5 | 2 | 3 | 2.0 |
| 6 | 2 | 4 | 5.3 |
| 7 | 2 | 5 | 4.4 |
| 8 | 3 | 4 | 4.4 |
| 9 | 3 | 5 | 3.7 |
| 10 | 4 | 5 | 4.5 |

I'm running the following query to get the three closest distances to patient # 3:

Select * from the distances where (patientIDa = 3 OR patientIDb = 3)
Order by distance
Limit 3;

Result set:

patient id patient ID distance
5 2 3 2
2 1 3 2.8
9 3 5 3.7

How to extract the patientIDx which corresponds to patientID3, since they are in a different column?
Run MySQL 5.6 on aws.

By the way, is this misconception to begin with? I'm fairly new to DB Tech.

In a PowerPoint chart, alternative background fill color for columns

I have been asked to reproduce this graph format in PowerPoint, but I can't figure out how to fill in the sections depending on the year?

A co-worker suggested hiding a solid rectangle in the background of the graph and updating it manually (or writing a script to move it), but I think there should be have a way in PowerPoint to stipulate that. Is there?

Target chart

This is what I have so far.

Current graph

Note: I am currently using the secondary axis and I cannot add a third to hide it as a bar graph going from 0 to 1 (it was an idea that I was had).

Google sheets – on the edit script for time stamping on multiple columns with individual time stamp based on the time of the change made, all having the same time stamp

I have checkboxes for all the other columns starting with column C and I want the date stamp to appear in column d of row 2. The script works fine for this, but I also have need to be for column e (check box) Column f Fills a date timestamp; column g (checkbox) column h fills the date timestamp. Originally, I found this to stop the automatic updating that Google's sheets do every day. Any help would be greatly appreciated!

function onEdit3() {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveCell();
  if( r.getColumn() != 8 ) 
{ 
var row = r.getRow();
var time = new Date();
time = Utilities.formatDate(time, "GMT+05:30", "dd/MM/yyyy, hh:mm:ss");
SpreadsheetApp.getActiveSheet().getRange("h" + row.toString()).setValue(time);
};
}

postgresql – Which function to use to have several "self-grouped" columns

I am looking for a function to use to solve the following problems. I have a table that has columns and data similar to this:

code1 | code2 | code3 | codeX etc
----------------------------------
   2  |   3   |   2   |
   3  |   1   |   2   |
   1  |   2   |   3   |
   2  |   3   |   1   |
   1  |   1   |   1   |
   3  |   1   |   1   |

thousands of rows of similar data, but I'm trying to apply COUNT + GROUP BY to get the following result:

code  | code1 | code2 | code3
----------------------------
   1  |   2   |   3   |  3
   2  |   2   |   1   |  2
   3  |   2   |   2   |  1

COUNT all of the above columns have been applied and are grouped together because they all have similar data. But the problem is that if I apply the following query:

SELECT code1 AS code, COUNT(code1), COUNT(code2), COUNT(code3) FROM table GROUP BY code1;

I get a table where all the values ​​of code2, code3, codeX are equal to the values ​​of column code1. And don't know which query should I use to get the desired results efficiently?

I am using PostgreSQL.